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.

Answer 1

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.

Answer 2

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")

Answer 3

Use DATE_FORMAT()

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

or

DATE_FORMAT(<Fieled>,'%r')

Answer 4

I had been trying to do the same and got this page returned from a Google search. I worked a solution for the time 21:00:00;

• using DATE_FORMAT(<field>,'%l.%i%p') which returned 9.00PM

• putting a LOWER() function around it to return 9.00pm

So the full code is; DATE_FORMAT(<field>,'%l.%i%p')

Worked OK for me ...

Answer 5

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