homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

MySQL timestamp and PHP

 11:26 am on Jul 18, 2006 (gmt 0)


I'm using MySQL in-built timestamp of 14 characters e.g., 20060713174545. Using PHP date() function, is it possible to get correct date out of it.

Does MySQL timestamp depends on Linux/ Windows machines?
Or I've to use PHP to build dates and manually insert into database.

Please suggest.



 12:16 pm on Jul 18, 2006 (gmt 0)

I personally store dates using the DATETIME field in MySQL which has the format "YYYY-MM-DD HH-MM-SS".

But, you can convert your timestamp easily.

echo date ("Y-m-d H:i:s", $sqlTimeStamp);


 12:22 pm on Jul 18, 2006 (gmt 0)

MySQL timestamps not work for me all times.

while( $row = mysql_fetch_array($result) ) {
echo date("Y-m-d H:i:s", $row['timestamp']) . "<br>";

Data in db is 20060713174545, 20060713174856, 20060713174856.

Output displayed in my browser is:
2038-01-19 08:44:07
2038-01-19 08:44:07
2038-01-19 08:44:07


 12:32 pm on Jul 18, 2006 (gmt 0)

echo "<h1>" . date("Y-m-d H:i:s", '20060713174545') . "</h1>";

See above also, 20060713174545 should mean year 2006, 13th of july etc.

What it displays on my Windows XP?
2038-01-19 08:44:07


 1:33 pm on Jul 18, 2006 (gmt 0)

I recently started using only the datetime format - because I think that is where mysql is going with the newer versions. But these two functions I use all the time to format dates of both types. I also have one that will convert the DATETIME to a unix timestamp.

function showdate($dated) // for straight timestamp 14
$hour = substr($dated,8,2);
$minute = substr($dated,10,2);
$second = substr($dated,12,2);
$month = substr($dated,4,2);
$day = substr($dated,6,2);
$year = substr($dated,0,4);
$mktime = mktime($hour, $minute, $second, $month, $day, $year);
$formatted = date("F j, Y g:i a",$mktime);
return $formatted;

function showdate_II($dated)// for the DATETIME format
$hour = substr($dated,11,2);
$minute = substr($dated,14,2);
$second = substr($dated,17,2);
$month = substr($dated,5,2);
$day = substr($dated,8,2);
$year = substr($dated,0,4);
$mktime = mktime($hour, $minute, $second, $month, $day, $year);
$formatted = date("F j, Y g:i a",$mktime);
return $formatted;


 6:06 pm on Jul 18, 2006 (gmt 0)

The MySQL "timestamp" field type is not a Unix "timestamp". Depending on which version of MySQL you have installed, it's more of a DATE field.

A traditional Unix timestamp, which is what PHP's date() depends on to work properly, is defined as "the number of seconds since January 1 1970 00:00:00 GMT", and is typically not very human-readable.

MySQL TIMESTAMP fields vary from release to release, it seems (this doc probably tells you the most about it [dev.mysql.com]).

Try converting the MySQL TIMESTAMP into a Unix timestamp and then making it human-readable:

$thistime=date("Y-m-d H:i:s", [url=http://www.php.net/manual/en/function.strtotime.php]strtotime[/url]($row["timestamp"]));

or something similar.


 6:31 pm on Jul 18, 2006 (gmt 0)

There is no need to do all this given the format anshul is printing though. If you simply print the string out, it will give you the exact format you are after:
// Instead of this: 
echo date("Y-m-d H:i:s", $row['timestamp']) . "<br>";
// do this:
echo $row['timestamp'];

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved