?
Solved

Converting MySQL Time to Standard, AM/PM

Posted on 2011-10-27
9
Medium Priority
?
662 Views
Last Modified: 2012-05-12
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
Comment
Question by:brendan-amex
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 81

Expert Comment

by:arnold
ID: 37042644
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
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 37042775
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
 

Author Comment

by:brendan-amex
ID: 37042942
hmm.. that code above fails, and not sure why...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 37042984
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
 
LVL 81

Expert Comment

by:arnold
ID: 37045017
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
 

Author Comment

by:brendan-amex
ID: 37045084
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 37045199
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
 

Author Closing Comment

by:brendan-amex
ID: 37045350
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37045958
Yep, that's the exact ticket!  Thanks for the points, ~Ray
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question