• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

Converting MySQL Time to Standard, AM/PM

Is there an easy way to convert the standard MySQL time to output on my php page as standard 12 hour, AM/PM time? I know it needs to be saved in my MySQL server as 24 hour time, but how can I change the output? Thanks.
0
brendan-amex
Asked:
brendan-amex
  • 3
  • 2
  • 2
  • +1
1 Solution
 
arnoldCommented:
you might be better to store the date/time in unix timestamp number of seconds since epoch january 1 1970 00:00:00 GMT
this is a bigint(11).

If you are storing the data as string, there are date/time functions
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
There are php functions that deal with date/time as well.
http://php.net/manual/en/ref.datetime.php

If date/time is not part of the query and just needs to be adjusted for the purpose of output to the browser, I'd suggest using the PHP date/time functions versus having the logic performed as part of the query on the database.

Try it for yourself to see which is more efficient.
0
 
COBOLdinosaurCommented:
See if this meets your needs:

function getTime ()
	{
	  	$result=mysql_query("SELECT TIME(someTimestamp) FROM mytable");
		if (!$result)
		{
			die('select failed!:' .mysql_error());
		}
		$row=mysql_fetch_array($result);
		if (!$row)
		{
			die ('row is empty!:'.mysql_error());
		}
		$timearr=explode(":",$row[0]);
		$time=($timearr[0] > 12) ? $timearr[0]-12.':'.$timearr[1].':'.$timearr[2].'pm':$timearr[0].':'.$timearr[1].':'.$timearr[2].'am';
		return $time;
	}

Open in new window

0
 
brendan-amexAuthor Commented:
hmm.. that code above fails, and not sure why...
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
COBOLdinosaurCommented:
Did you do a connect to the DB server first?  The code assumes there is already a connection. It works when I run it against my DB update timestamps.
0
 
arnoldCommented:
Did you adjust the column names to match what you have?
The other is that the data in the column is not in timestamp format but is a date/time so code example without knowing the table structure is premature.

select TIME(UNIX_TIMESTAMP());
select TIME(NOW());

gives different results.
0
 
brendan-amexAuthor Commented:
Yes, I first create the DB connection then the function below that but the webpage returns an error. Here's what I did (below). I'm coding in Notepad++ and everything after the $time variable is greyed out, telling me that something is wrong with that line? Or did I implement it in wrong?
<?
$dbc = mysql_connect("localhost", "username", "pass") or die ('Error #1'); 
mysql_select_db("datastore", $dbc) or die ('Error #2');

function getTime ()
	{
	  	$result=mysql_query("SELECT TIME(NOW()) AS TEST");
		if (!$result)
		{
			die('select failed!:' .mysql_error());
		}
		$row=mysql_fetch_array($result);
		if (!$row)
		{
			die ('row is empty!:'.mysql_error());
		}
		$timearr=explode(":",$row[0]);
		$time=($timearr[0] > 12) ? $timearr[0]-12.':'.$timearr[1].':'.$timearr[2].'pm':$timearr[0].':'.$timearr[1].':'.$timearr[2].'am';
		return $time;
	}

	mysql_close($dbc);

?>

Open in new window

0
 
Ray PaseurCommented:
See if this gives you some ideas about how to handle DATETIME values in PHP and MySQL (hint: it is easier than you think)...
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
brendan-amexAuthor Commented:
Perfect. Thank you Ray.

$iso_datetime = '2011-10-28 08:01:44';

$unix_timestamp = strtotime($iso_datetime);
$pretty_date = date('l, F jS, Y \a\t g:i:sa', $unix_timestamp);
echo $pretty_date;
0
 
Ray PaseurCommented:
Yep, that's the exact ticket!  Thanks for the points, ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now