Welcome to WebmasterWorld Guest from 54.198.240.21

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL timestamp and PHP

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

Full Member

10+ Year Member

joined:July 25, 2004
posts:311
votes: 0


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.

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

Full Member

10+ Year Member

joined:Feb 24, 2005
posts:225
votes: 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 July 18, 2006 (gmt 0)

Full Member

10+ Year Member

joined:July 25, 2004
posts:311
votes: 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 July 18, 2006 (gmt 0)

Full Member

10+ Year Member

joined:July 25, 2004
posts:311
votes: 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 July 18, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 20, 2003
posts:132
votes: 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 July 18, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 20, 2004
posts:1475
votes: 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 July 18, 2006 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12541
votes: 1


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