Translating MySQL timestamp to datetime

Hi All,

Any thoughts on the easiest way to translate a MySQL timestamp (which looks
like 20040422090941) to the datetime format (which looks like 2004-04-22
09:09:41). This is just to make it easier for a human to read it.

I have thought about splitting it into chunks using something like
str_split, then piecing it back together, but it seems like there should be
an easier way. And also, I am not running PHP5.

I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had no
luck. strtotime cannot handle that format.

This seems like the sort of thing you should be able to do in 1 line of
code. Any pointers?

-Josh

re: Translating MySQL timestamp to datetime

I noticed that Message-ID: <MxPhc.44294$L31.36599@nwrddc01.gnilink.net>
from Joshua Beall contained the following:
[color=blue]
>I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had no
>luck. strtotime cannot handle that format.[/color]

Try converting it to a UNIX timestamp in the query e.g
SELECT UNIX_TIMESTAMP(mysql_timestamp)) AS unixtimestamp...



--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

re: Translating MySQL timestamp to datetime

"Geoff Berrow" <blthecat@ckdog.co.uk> wrote in message
news:hehf80tvedssi6vpt7k9qij4hl0j2o2og3@4ax.com...[color=blue]
> I noticed that Message-ID: <MxPhc.44294$L31.36599@nwrddc01.gnilink.net>
> from Joshua Beall contained the following:
>[color=green]
> >I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had[/color][/color]
no[color=blue][color=green]
> >luck. strtotime cannot handle that format.[/color]
>
> Try converting it to a UNIX timestamp in the query e.g
> SELECT UNIX_TIMESTAMP(mysql_timestamp)) AS unixtimestamp...[/color]

Is this function an ANSI standard? Or a MySQL specific extension?

I try to avoid the MySQL specific stuff. Not religiously, but when
alternatives exist, I opt for the standards-compliant way of doing it.

re: Translating MySQL timestamp to datetime

"Joshua Beall" <jbeall@donotspam.remove.me.heraldic.us> wrote:
[color=blue]
> Any thoughts on the easiest way to translate a MySQL timestamp (which
> looks like 20040422090941) to the datetime format (which looks like
> 2004-04-22 09:09:41). This is just to make it easier for a human to
> read it.[/color]

SELECT DATE_FORMAT(timestampfield, '%Y-%m-%d %H:%i:%s') AS dtime FROM
table;

HTH;
JOn

re: Translating MySQL timestamp to datetime

Joshua Beall a écrit:[color=blue]
> Hi All,
>
> Any thoughts on the easiest way to translate a MySQL timestamp (which looks
> like 20040422090941) to the datetime format (which looks like 2004-04-22
> 09:09:41). This is just to make it easier for a human to read it.
>
> I have thought about splitting it into chunks using something like
> str_split, then piecing it back together, but it seems like there should be
> an easier way. And also, I am not running PHP5.
>
> I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had no
> luck. strtotime cannot handle that format.[/color]

NO NO : "20040422090941" is *NOT* a timestamp ! but just a string of
(numeric) chars ! So you can't have success.

re: Translating MySQL timestamp to datetime

"Joshua Beall" <jbeall@donotspam.remove.me.heraldic.us> schrieb im
Newsbeitrag news:MxPhc.44294$L31.36599@nwrddc01.gnilink.net...[color=blue]
> Hi All,
>
> Any thoughts on the easiest way to translate a MySQL timestamp (which[/color]
looks[color=blue]
> like 20040422090941) to the datetime format (which looks like 2004-04-22
> 09:09:41). This is just to make it easier for a human to read it.
>
> I have thought about splitting it into chunks using something like
> str_split, then piecing it back together, but it seems like there should[/color]
be[color=blue]
> an easier way. And also, I am not running PHP5.
>
> I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had[/color]
no[color=blue]
> luck. strtotime cannot handle that format.
>
> This seems like the sort of thing you should be able to do in 1 line of
> code. Any pointers?
>[/color]

Have a look at strftime. It's quite tricky at first but specially if you
ever have to do a multilanguage application you will love it.

HTH
Markus

re: Translating MySQL timestamp to datetime

Hi Joshua,

try "select UNIX_TIMESTAMP(myDate) as unixdate from ..."
then you can use the function strftime() in PHP to show the unix
timestamp in readable format.

Other method would be: "select DATE_FORMAT(myDate, '%Y-%m-%d') from
...." which will create the user-readable field directly.

Regards

Marian
On Thu, 22 Apr 2004 13:13:16 GMT, "Joshua Beall"
<jbeall@donotspam.remove.me.heraldic.us> wrote:
[color=blue]
>Hi All,
>
>Any thoughts on the easiest way to translate a MySQL timestamp (which looks
>like 20040422090941) to the datetime format (which looks like 2004-04-22
>09:09:41). This is just to make it easier for a human to read it.[/color]

--
Internet-Dienstleistungen - von der Webseite bis zum Online-Shop
http://www.heddesheimer.de mailto:marian@heddesheimer.de

re: Translating MySQL timestamp to datetime

"Joshua Beall" <jbeall@donotspam.remove.me.heraldic.us> wrote in message
news:MxPhc.44294$L31.36599@nwrddc01.gnilink.net...[color=blue]
> Hi All,
>
> Any thoughts on the easiest way to translate a MySQL timestamp (which[/color]
looks[color=blue]
> like 20040422090941) to the datetime format (which looks like 2004-04-22
> 09:09:41). This is just to make it easier for a human to read it.
>
> I have thought about splitting it into chunks using something like
> str_split, then piecing it back together, but it seems like there should[/color]
be[color=blue]
> an easier way. And also, I am not running PHP5.
>
> I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had[/color]
no[color=blue]
> luck. strtotime cannot handle that format.
>
> This seems like the sort of thing you should be able to do in 1 line of
> code. Any pointers?
>
> -Josh[/color]

Grabbed from http://www.weberdev.com/get_example.php3/1427

function revertTimeStamp($timestamp)
{
$year=substr($timestamp,0,4);
$month=substr($timestamp,4,2);
$day=substr($timestamp,6,2);
$hour=substr($timestamp,8,2);
$minute=substr($timestamp,10,2);
$second=substr($timestamp,12,2);
$newdate=mktime($hour,$minute,$second,$month,$day, $year);
return($newdate);
}

--
Andrew @ Rockface
np: Günter Neuhold / Badische Staatskapelle - Erste Szene ; Hehe! Ihr
Nicker!
andrew@rockface-records.co.uk
www.rockface-records.co.uk