Convert MySQL 14 digit timestamp into Readable format

[mjurmann]

Hello. I am trying to convert a 14 digit timestamp located in a field in my MySQL database. This is the format I'm using right now to convert it to a readable format: <?php echo date("D M j G:i:s T Y",$row_Recordset4['date_posted']);?> I'm getting Mon Jan 18 22:14:07 EST 2038 as the result, which is not correct. Can someone please tell me why I'm getting an incorrect time (the year 2038? come on!) and how I can fix this.

 

I've been combing google and these forums all night and can't figure it out...

 

Thanks much

[PC Nerd]

well i think the problem is either with the Mysql database, or your server's time is incorrect

[hvle]

The PHP date function take a string format and a unix timestamp.

 

Unix timestamp is an integer, ie.  1342385029.

The 'date_posted' retrieved from mysql is the format of mysql, probably human readable like "2007-02-04 10:40:05 am".  You can not use this and passed into php date function and expect it to work right.

 

you have to convert that mysql time stamp into unix timestamp in order to for php's date to work.

Take a look at php's strtotime function.

 

[mjurmann]

I'm researching this now, but it is not making much sense to me. Does anyone have a code example that I could use to make this work?

[sasa]

try

<?php echo date("D M j G:i:s T Y",strtotime($row_Recordset4['date_posted']));?>

[snakebit]

I don't understand something , you have 14 digit number with out any separators, right. And how you get this number? Is it in any format or is's just 2003-01-01 00:00:00 -> 20030101000000 ? Because if you make it in this way you can separate it with php functions.

[hvle]

There is a function in mysql that convert your time stamp directly to Unix TS and you can use with any of php function:

UNIX_TIMESTAMP();

 

select UNIX_TIMESTAMP(date_posted) as unixTS from .....

[ShogunWarrior]

It seems that if you have a 14 digit number then it is larger than a 32 bit integer and so the date function can't handle it.

It shouldn't be 14 digits.

[richardw]

(2038 is the default last year that I have been able to test scripts through, so  it souns like its defaulting to the end)

 

Try adding it to the query, such as:

 

"SELECT  *, date_format(begindate, '%m/%d/%Y'), date_format(enddate, '%m/%d/%Y') FROM road"

 

Then, echo back: <?php  echo $row["date_format(begindate, '%m/%d/%Y')"]; ?>

 

This works with the UNIX 14 digit timestamp.

 

Alternately, you can my convoluted method (works create with many calendar scripts if you bring the date field in for editing):

 

$daystring = $row["begindate"];

$month = substr($daystring, 4, 2);

$day = substr($daystring, 6, 2);

$year = substr($daystring, 0, 4);

THEN:

<?php echo $month,"-",$day,"-",$year ?>

 

I hope this works for you. 

[mjurmann]

Alright, I've tried SELECT *, date_format(date_posted, '%m/%d/%Y') FROM news WHERE id != %s and it's telling me that I have an error in my SQL syntax. Does anyone see where this error is?

[BillyBoB]

maybe i could help i have had this error b4 try this code and tell my wat u get out of it exactly:

 

<?php
$news = mysql_query("SELECT * FROM news WHERE id= 1"); 
$newsarr = mysql_fetch_array($news);
echo $newsarr[date];
?>

replace 1 with a valid id!!!

replace date with the correct value like whatever the the date is stored as!!!

 

Then reply if u get the same thing as i did then i will tell u from there