Translating MySQL timestamp to datetime

[Joshua Beall]

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",strtotim e("200404220909 41")), 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

[Geoff Berrow]

Re: Translating MySQL timestamp to datetime

I noticed that Message-ID: <MxPhc.44294$L3 1.36599@nwrddc0 1.gnilink.net>
from Joshua Beall contained the following:
[color=blue]
>I already tried date("Y-m-d H:i:s",strtotim e("200404220909 41")), 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/

[Joshua Beall]

Re: Translating MySQL timestamp to datetime

"Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
news:hehf80tved ssi6vpt7k9qij4h l0j2o2og3@4ax.c om...[color=blue]
> I noticed that Message-ID: <MxPhc.44294$L3 1.36599@nwrddc0 1.gnilink.net>
> from Joshua Beall contained the following:
>[color=green]
> >I already tried date("Y-m-d H:i:s",strtotim e("200404220909 41")), 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.

[Jon Kraft]

Re: Translating MySQL timestamp to datetime

"Joshua Beall" <jbeall@donotsp am.remove.me.he raldic.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(tim estampfield, '%Y-%m-%d %H:%i:%s') AS dtime FROM
table;

HTH;
JOn

[Paul Delannoy]

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",strtotim e("200404220909 41")), 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.

[Markus Ernst]

Re: Translating MySQL timestamp to datetime

"Joshua Beall" <jbeall@donotsp am.remove.me.he raldic.us> schrieb im
Newsbeitrag news:MxPhc.4429 4$L31.36599@nwr ddc01.gnilink.n et...[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",strtotim e("200404220909 41")), 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

[Marian Heddesheimer]

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(myD ate, '%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@donotsp am.remove.me.he raldic.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-Dienstleistunge n - von der Webseite bis zum Online-Shop
http://www.heddesheimer.de mailto:marian@h eddesheimer.de

[Andrew @ Rockface]

Re: Translating MySQL timestamp to datetime

"Joshua Beall" <jbeall@donotsp am.remove.me.he raldic.us> wrote in message
news:MxPhc.4429 4$L31.36599@nwr ddc01.gnilink.n et...[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",strtotim e("200404220909 41")), 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($t imestamp,0,4);
$month=substr($ timestamp,4,2);
$day=substr($ti mestamp,6,2);
$hour=substr($t imestamp,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

https://www.rockface-records.co.uk