Formatting a Time() Value

kb posted this at 21:15 — 31st January 2004.

Joined: Feb 2002

Hey, I have a MySQL dB that has a field called 'time' with the value of time. I need to pull it out and format it to display like: hour:min am/pm

I checked the mysql.com documentation, and tried a bunch of things. This is the last working one that didn't produce an error, but shows nothing as a result. I have been inputting values in this dB on a 1-24 scale (hours).

<?php
$sql = mysql_query(\"SELECT TIME_FORMAT(time, '%l:%i %p')....
?>

why doesn't that work?
Thanks for the help.

xxgeek posted this at 21:27 — 31st January 2004.

He has: 17 posts

Joined: Jan 2004

Not exactly sure if this works but give it a shot:

<?php
$sql = \"SELECT DATE_FORMAT(time,'%b %D, %Y at %T hrs')
?>

hope it helps...

ohh i think this should be
:

<?php
$sql = \"SELECT DATE_FORMAT(time,'%T hrs')
?>

VENI VIDI VICI

Suzanne posted this at 23:26 — 31st January 2004.

She has: 5,507 posts

Joined: Feb 2000

http://www.mysql.com/doc/en/Date_and_time_functions.html

<?php
$sql = mysql_query(\"SELECT DATE_FORMAT(time, '%l:%i %p')\");
?>

There is no TIME_FORMAT() function.

Of course, if your field isn't a TIME, DATE, DATETIME, or TIMESTAMP field, you may have issues.

kb posted this at 01:17 — 1st February 2004.

He has: 1,380 posts

Joined: Feb 2002

if there is no time format function...

Quote: TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12:

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'

(same document you posted)

thanks.

Suzanne posted this at 05:08 — 1st February 2004.

She has: 5,507 posts

Joined: Feb 2000

Well, fancy that, lol. Serves me right for posting on a Saturday and not paying attention. Did you get the problem solved?

kb posted this at 00:31 — 5th February 2004.

He has: 1,380 posts

Joined: Feb 2002

Wait...there was somethinig about PHP that I thought and read about...and I was right!?!? No way....

Lol, yea it works fine. Thanks.