Format a Time value from MySQL database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ganzina
Forum Newbie
Posts: 8
Joined: Mon Nov 04, 2002 4:18 pm
Location: California

Format a Time value from MySQL database

Post 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!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
ganzina
Forum Newbie
Posts: 8
Joined: Mon Nov 04, 2002 4:18 pm
Location: California

Post 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!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
ganzina
Forum Newbie
Posts: 8
Joined: Mon Nov 04, 2002 4:18 pm
Location: California

Post by ganzina »

Thanks!
Post Reply