datetime implementation in php mysql

Question

how can i get only the time from the datetime variable? i am storing the datetime in the database. If i only need to fetch the time, how can I do it?

I have to run a single query in which I want to fetch the date and time from the datetime column. i need to use the date and time in different fields.

Answer 1

select TIME(fieldname) from tablename

or use the date_format() function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Answer 2

Since Scott gave a SQL-based solution, there's a dead-simple way to get the time as a string in php from a MySQL datetime-formatted field.

$str = '2010-08-06 16:42:21';
list($date, $time) = explode(' ',$str);
echo $time;

Since the formatting for MySQL's datetime field follows php's date('Y-m-d H:i:s');, a simple explode() on space provides two separate pieces.. one of the date, and one of the time.

Answer 3

A better solution will be to use mysql function for this, so it will be faster.

SELECT DATE_FORMAT('your_date_column', '%H:%i:%s') AS time FROM table;