Page 1 of 1

Format a Time value from MySQL database

Posted: Thu Oct 23, 2003 12:22 am
by ganzina
I have a TIME value in a MySQL database, such as 09:00:00 and I'd like to show it as 9:00AM. I have looked at the PHP commands for something simple, but I couldn't find anything (except parsing out the hours, minutes, seconds and add the date).

Anyone have a solution for me??

Thanks!

Posted: Thu Oct 23, 2003 2:59 am
by twigletmac
You can use MySQL to format the time, have a look at the date and time functions, specifically, TIME_FORMAT():

http://www.mysql.com/doc/en/Date_and_ti ... tions.html

Mac

Posted: Thu Oct 23, 2003 3:02 am
by JayBird
try this

Code: Select all

select *, TIME_FORMAT(fieldname, '%k:%i:%s %p') AS display_fieldname from tablename
then you can access your formated date as display_filedname.

Mark

Posted: Thu Oct 23, 2003 11:33 am
by ganzina
Thanks for the tip. I tried it and it does work except that I need to do a query first for my page.

Right now, I have:
$newquery="SELECT * FROM schedule WHERE schedule_day ='$i' ORDER BY schedule_starttime";
$newresult=mysql_query($newquery);

Where do I fit in the "TIME_FORMAT" part? I tried it a few times and it didn't seem to work with my query. Is there another php command that I can use to format the time?

Thanks!

Posted: Thu Oct 23, 2003 4:04 pm
by JayBird
try this

Code: Select all

$newquery="SELECT *, TIME_FORMAT(fieldname, '%k:%i:%s %p') AS display_fieldname  FROM schedule WHERE schedule_day ='$i' ORDER BY schedule_starttime"; 
$newresult=mysql_query($newquery);
..and, as i said before, your formated time will be available as display_fieldname.

don't forget to change the fieldname in the line that starts "TIME_FORMAT(fieldname" to the field you are formatting

Mark

Posted: Fri Oct 24, 2003 10:35 am
by ganzina
Thanks!