To convert a MySQL timestamp

[ljCharlie]

Currently my MySQL database Time column format is HH:MM:SS and when I query the database, I want the Time column to display something like this: HH:MM AM/PM but so far I wasn't able to do it. I used the UNIX_TIMESTAMP(Time_column) AS ljTime in my SQL query but when it display on the webpage it showed 0 0 PM and it suppose to show 07:00 PM. I have also tried this: DATE_FORMAT(ljTime, '%l:%i %p') AS ljTime but then when it show on the webpage, every TIME column is dispaly as 12:00 AM. Any help will be appreciated.

Many thanks in advance!

ljCharlie

 

[sleipnir214]

Your DATE_FORMAT use is correct.

Verify that your database has the values you expect.

Verify that your SQL query works from PHP by printing it out, cutting it, and pasting it into your favorite MySQL admin tool.

Verify that your fetch statements in PHP are getting the values they should using print_r() or similar functions.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[KarveR]

I'm sure before now I had an issue with DATE_FORMAT() and AS , just humour me for a mo, remove the 'AS ljTime' and see if your time is correctly reported.
You may need to set the value for the time using a user variable :
@ljTime:=DATE_FORMAT(ljTime, '%l:%i %p')

instead.

//karv

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[ljCharlie]

It works now, thanks! Actually it's not Date_Format() but Time_Format()

Chong