Welcome to WebmasterWorld Guest from 54.205.60.49

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL timestamp and PHP

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

10+ Year Member



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 Jul 18, 2006 (gmt 0)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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)

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



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

Featured Threads

Hot Threads This Week

Hot Threads This Month