Converting mysql TIME from 24 HR to AM/PM format

Question

I want to display the TIME field from my mysql table on my website, but rather than showing 21:00:00 etc I want to show 8:00 PM. I need a function/code to do this or even any pointers in the right direction. Will mark the first reply with some code as the correct reply.

Solution 1

Check this out: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

I'd imagine you'd want date_format().

Example: DATE_FORMAT($date, "%r")

Solution 2

Show the date & time data in AM/PM format with the following example...

SELECT DATE_FORMAT(`t`.`date_field`,'%h:%i %p') AS `date_field` FROM `table_name` AS `t`

OR

SELECT DATE_FORMAT(`t`.`date_field`,'%r') AS `date_field` FROM `table_name` AS `t`

Both are working properly.

Solution 3

You can also select the column as a unix timestamp using MYSQL's UNIX_TIMESTAMP() function. Then format it in PHP. IMO this is more flexible...

select a, b, c, UNIX_TIMESTAMP(instime) as unixtime;

The in PHP use the date() function & format it any way you want.

<?php echo date('Y/m/d', $row->unixtime); ?>

The reason I like this method as opposed to formatting it in SQL is b/c, to me, the date's format is a display decision & (in my opinion) formatting the date in SQL feels wrong... why put display logic in your SQL?

Now - if you're not processing the data in PHP and are doing adhoc queries then DATE_FORMAT() is the way to go. But if you're gonna have the data show up on the web I'd go with UNIX_TIMESTAMP() and do the formatting in PHP...

I mean... lets say you want to change how the date & time are displayed on the page... wouldn't it feel "off" to have to modify your SQL for a display tweak?

my 2 cents

Solution 4

Use DATE_FORMAT()

DATE_FORMAT(<Fieled>,'%h:%i:%s %p')

or

DATE_FORMAT(<Fieled>,'%r')

View more solutions