date from timestamp

[Matty]

having some problems displaying a date and time from a timestamp(14) in mysql.

when i access the db record, and display the timestamp "as-is", it shows, for example, "20050112113856".

now if i use any of the date functions on this timestamp value, it comes out incorrectly.

for example:

$thisdate=getdate($myrow[time_ordered]);
echo($thisdate['year']);

shows up as 2038 instead of 2005.

what is the correct way of manipulating this timestamp value to get the date and time out of it?

thanks,
Matty

[pj59]

Hello!

The timestamp is a long integer containing the number of seconds between the Unix Epoch (January 1 1970 00:00:00 GMT) and the time specified. - Taken from php.net

Your timestamp is a string first of all. Even if you made that one an integer, imagine, what it will represent: it will be 20,050,112,113,856 seconds from January 1 1970 00:00:00 GMT and this is in 2038.

If you do not want to replace all your "timestamps" with something, PHP would swallow, you may have a play with this:<?php
$thisdate=getdate("20050112113856");
echo($thisdate['year']).'<br>';
echo($thisdate['month']).'<br>';
echo($thisdate['mday']).'<br>';
$thisdate="20050112113856";
echo $thisdate.'<br>';
$y=substr($thisdate,0,4);
$m=substr($thisdate,4,2);
$d=substr($thisdate,6,2);
$h=substr($thisdate,8,2);
$mi=substr($thisdate,10,2);
$s=substr($thisdate,12,2);
$realTimestamp=mktime ($h,$mi,$s,$m,$d,$y);
echo $realTimestamp.'<br>';
$thisdate=getdate($realTimestamp);
echo($thisdate['year']).'<br>';
echo($thisdate['month']).'<br>';
echo($thisdate['mday']).'<br>';
?>Regards PJ

[NogDog]

Your best bet would be to let MySQL do the work (http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html) for you:

# get date in format mm/dd/yyyy:
$query = "SELECT col1, DATE_FORMAT(date_time, '%m/%d/%Y') FROM table WHERE col1 = 'something'";
$result = mysql_query($query);

[pj59]

Hey NogDog!

I am a newcomer to PHP and almost completely blind as far as MySQL is concerned. Even though your link was primarily meant for Matty (I guess), thanks very much for it.

Regards PJ

[NogDog]

Originally posted by pj59
Hey NogDog!

I am a newcomer to PHP and almost completely blind as far as MySQL is concerned. Even though your link was primarily meant for Matty (I guess), thanks very much for it.

Regards PJ
Hey, it's a public forum: my answers are for anyone who can use them. (Caveat emptor, of course. ;) )

[pj59]

:D