Time conversion problem

[kannuk]

Hi - I have been searching for a way to select the stored time from a database (stored in 24h format - 13:20:00) and display it as 12h format. I have checked a number of forums and nothing quite seems to fit the bill. I would like it to display as 2:00pm, 12:00am, etc. Here is what I have so far:

 

  $sql = ('SELECT * FROM events WHERE active="1" AND event_date >= CURRENT_DATE AND MONTH(event_date) = 1 order by event_date ASC, event_start ASC');

  $result = mysql_query($sql);

  $num_rows = mysql_num_rows($result);

 

  // Yes Item

  if (!($num_rows == 0)) {

$myrow = mysql_fetch_array($result);

echo ('<span class="bold" style="font-size:14px">January</span>');

do {

  printf ('<span>Event Time: %s', $myrow['event_start']);

  if ($myrow['event_end'] != '') {

  printf (' to %s', $myrow['event_end']);

  }

}

while ($myrow = mysql_fetch_array($result));

  } 

 

Right now this just shows the regular 24h format (hh:mm:ss) for any events in the month of January. I would like both "event_start" and "event_end" to show up in 12h format. I have tried a number of things but none of it works. I'm SURE it is something simple that I have missed. Thanks in advance for any help.

[litebearer]

Have you tried ...

http://www.php-mysql-tutorial.com/wikis/php-tutorial/php-12-24-hour-time-converter-part-1.aspx

[Pikachu2000]

Select it in your query already formatted using the MySQL DATE_FORMAT() function.

[kannuk]

Hey - thanks for the feedback. litebearer, I am having a hard time getting my head around what to do with that code. I am not that experienced at PHP so I am sort of drawing a blank.

 

Pickachu2000 - I have been trying to include DATE_FORMAT in the query but it is not working (at least not how I am doing it. Doesn't this have to be done below where the information is printed out, not in the query? Here is what I tried to include in the query: DATE_FORMAT(event_start, '%r'). I also tried with TIME_FORMAT.

 

Sorry I'm just not getting it.

[Pikachu2000]

Actually, I guess I should ask what the data type of the field is that the time is stored in. Is it a DATETIME field, or something else?

[kannuk]

Hi Pickachu -

 

It's TIME. Sorry, I thought I stated that upfront. Stored as 00:00:00 (h/m/s) 24 hour format.

[litebearer]

simply put the function near the top of your page; then fill the variable with the data from your db table (see the last couple of lines)

<?php
function TimeCvt ($time, $format)   {
#   $time   - String:  Time in either 24hr format or 12hr AM/PM format
#   $format - Integer: "0" = 24 to 12 convert    "1" = 12 to 24 convert
#   RETURNS Time String converted to the proper format
if (ereg ("[0-9]{1,2}:[0-9]{2}:[0-9]{2}<wbr />", $time))   {
	$has_seconds = TRUE;
}else{
	$has_seconds = FALSE;
}
if ($format == 0)   {         //  24 to 12 hr convert
	$time = trim ($time);
	if ($has_seconds == TRUE)   {
		$RetStr = date("g:i:s A", strtotime($time));
	}else{
		$RetStr = date("g:i A", strtotime($time));
	}
}elseif ($format == 1){
	// 12 to 24 hr convert
	$time = trim ($time);
	if ($has_seconds == TRUE){
		$RetStr = date("H:i:s", strtotime($time));
	}else{
		$RetStr = date("H:i", strtotime($time));
	}
}
return $RetStr;
}

$bad_time = "14:32:11"; /* the value from your db table */
$format = 0;
$good_time = TimeCvt ($time, $format);
?>

[Pikachu2000]

While that will work, I would think this would be quite a bit less hassle.

"SELECT TIME_FORMAT(`event_start`, '%l:%i %p') AS `s_time`, TIME_FORMAT(`event_end`, '%l:%i %p') AS `e_time` . . . etc.

The formattted times can then be accessed directly as $myrow['s_time'] and $myrow['e_time'].