[SOLVED] Format TIME field from MySQL as 12-hour

[mikera]

I have a TIME field in MySQL that is stored in 24-our format that I want to display as 12-hour format on my site.  Is there a simple way to format the time correctly?  I've seen plenty of examples of how to format a DATETIME field, but nothing for just a TIME field.

 

Thanks,

Mike

[trq]

SELECT DATE_FORMAT(fldname,'%h:%i %p') AS ftime FROM tbl;

[mikera]

OK, so I'm fairly new to php so I need a little extra help to get this to work.  Here is the code I have currently that just pulls the value from the table.  How do I use your code in conjunction with this?

 

<?php echo $match->gametime;?>

 

Earlier on the page I have the $match table variable defined and gametime is the field name.  I'm just not sure where to put the code and how to echo it.

[trq]

We need to see the query that eventually creates this $match object.

[mikera]

Unfortunately I can't locate it.  This page is part of a Joomla component so there are a lot of external references that I don't quite understand yet.

 

Is there no way to just format a TIME value that is passed from a table like there seems to be for DATETIME?  I was hoping there was just a simple syntax like:

 

formatTIME(fieldname, %r)

 

...where %r is the 12-hour format variable.  That way I could end up with something basic like:

 

<?php echo formatTIME($match->gametime, %r);?>

 

My ignorance is really on display now!

 

Mike

[drewbee]

try strtotime()

 

$dateTimestamp = strtotime($match->gametime);

 

$dateFormat = date($dateTimestamp, "h:i:s");

 

echo $dateFormat;

[mikera]

I tried and it gave me a long integer "1185084000" regardless of the time value in the field.

 

Maybe it would help if I told you I was storing the time as "09:30:00" or "17:45:00" in the field "$matches->gametime".  What I want to display in those examples would be "9:30 am" and "5:45 pm".  Leading zeros would be OK, too: "09:30 am" and "05:45 pm".

 

Thanks,

Mike

[Hypnos]

try strtotime()

 

$dateTimestamp = strtotime($match->gametime);

 

$dateFormat = date($dateTimestamp, "h:i:s");

 

echo $dateFormat;

Right idea, but your date parameters were backwards.

 

echo date("h:i a", strtotime($match->gametime));

[mikera]

Almost there, Hypnos.  But that code just gives me 12:00 am for every value.  Remember, my field is only a TIME field, not a DATETIME field, so it seems all the date() functions are ignoring the time value as it is stored as "hh:mm:ss".  Is there a similar function for TIME fields in MySQL?

[Hypnos]

Oh, right. I missed that.

 

echo date("h:i a", strtotime("2004-01-20 " . $match->gametime));

 

The date doesn't matter.

[mikera]

THANKS HYPNOS!  Your first suggestion actually worked.  I forgot to change the page to one that had the times populated.  I don't actually need to add the dummy date into the code.

 

Thanks again!

Mike