By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,643 Members | 1,797 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,643 IT Pros & Developers. It's quick & easy.

Translating MySQL timestamp to datetime

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
I noticed that Message-ID: <Mx*******************@nwrddc01.gnilink.net>
from Joshua Beall contained the following:
I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had no
luck. strtotime cannot handle that format.


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/
Jul 17 '05 #2

P: n/a
"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:he********************************@4ax.com...
I noticed that Message-ID: <Mx*******************@nwrddc01.gnilink.net>
from Joshua Beall contained the following:
I already tried date("Y-m-d H:i:s",strtotime("20040422090941")), and had noluck. strtotime cannot handle that format.


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


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.
Jul 17 '05 #3

P: n/a
"Joshua Beall" <jb****@donotspam.remove.me.heraldic.us> wrote:
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.


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

HTH;
JOn
Jul 17 '05 #4

P: n/a
Joshua Beall a écrit:
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.


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

Jul 17 '05 #5

P: n/a
"Joshua Beall" <jb****@donotspam.remove.me.heraldic.us> schrieb im
Newsbeitrag news:Mx*******************@nwrddc01.gnilink.net...
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?


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
Jul 17 '05 #6

P: n/a
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"
<jb****@donotspam.remove.me.heraldic.us> wrote:
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.


--
Internet-Dienstleistungen - von der Webseite bis zum Online-Shop
http://www.heddesheimer.de mailto:ma****@heddesheimer.de
Jul 17 '05 #7

P: n/a
"Joshua Beall" <jb****@donotspam.remove.me.heraldic.us> wrote in message
news:Mx*******************@nwrddc01.gnilink.net...
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


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!
an****@rockface-records.co.uk
www.rockface-records.co.uk
Jul 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.