Converting mysql TIME from 24 HR to AM/PM format

Hi,

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.

From stackoverflow Ali

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

  From Steve Klabnik

• Use DATE_FORMAT()

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

  or

  DATE_FORMAT(<Fieled>,'%r')

  From Martin York

• 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

  Ali : I have a class that contains all of my date handling code. In the login code I just call a function from that class which formats the time using DATE_FORMAT(). So it doesn't really show in the display code and doesn't feel off Mysql dates are just more readable than unix timestamps.
  From arin sarkissian

• 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(,'%l.%i%p') which returned 9.00PM

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

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

  Worked OK for me ...

  From $pageUsers[$post.author].name

• use the date_format function, use this site to help you format it http://www.mysqlformatdate.com

  From gerard

• use http://www.mysqlformatdate.com to help you format the date

  From Gerard