Reterive date and time part seprately from TimeStamp

jimjack145

Hi All,

I have two tables called users and punch. In a punch table i have two columns one is called created_date and other is time_stamp.

'created_date' column store value in format of "1205299150" with type of int (11)
and
time_stamp column store value in format of "2008-03-12 08:45:00" with type timestamp

I want to display time_stamp column values in to two different columns 1) Time and 2) Date.

Can any one tell me what query i have to run for the same. I am currently using below mentioned query:

SELECT users.id, users.first_name, users.last_name, punch.created_date, punch.time_stamp
FROM users INNER JOIN punch on users.id = punch.created_by
WHERE users.id = '1'

Regards,
Jimi

bpat1434

Use the MySQL DATE_FORMAT() functions.

jimjack145

bpat1434 wrote:
Use the MySQL DATE_FORMAT() functions.

Hi,

Thanks for your reply. Its work.

I need one more help if possible. How do i display specific period days with search criteria?

Also how do i convert timestamp into indian time?

Regards,
Jimi

bpat1434

If it's a timestamp that is just seconds since unix epoch, then you can just subtract (or add) the number of seconds to it that is your offset. For example, I'm in EST, so I'd subtract 5 hours worth of seconds (or 60605) from the timestamp, and display that. I'm not sure what India's timezone is.

As for searching for a date, you could use the BETWEEN mySQL keyword, or you could turn all times into timestamps. Then do this kind of query:

SELECT * FROM `table` WHERE `timestamp` >= 'start_time' AND `timestamp`<= 'end_time'