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!