MySQL timestamp and PHP

[anshul]

Friends,

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.

[Sekka]

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);

[anshul]

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

[anshul]

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

[slade7]

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;
}

[StupidScript]

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.

[coopster]

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'];