convert mysql timestamp into actual date and time?

Question

I have dates stored in a mysql table, they are set to store as CURRENT TIMESTAMP in each row and are stored as follows:

2010-05-29 01:17:35

but what i am trying to do is somehow use PHP to be able to seperate everything and create a date more like:

May 29 2010 1:17 AM

can anyone at least direct me in the right path that i should take. any help is greatly appreciated!

Kessiah

Answer 1

echo date('M j Y g:i A', strtotime('2010-05-29 01:17:35'));

http://php.net/manual/en/function.date.php

Toluidine

Be sure to check out Localized Notations in the PHP manual on the Compound Formats page php.net/manual/en/datetime.formats.compound.php – Rycca

Answer 2

You have two solutions :

• Use strtotime() to parse the date to a timestamp, and date() to re-format it to a string
• Or use the DateTime class

In PHP code, this would mean using :

echo date('M j Y g:i A', strtotime('2010-05-29 01:17:35'));

Or :

$dt = new DateTime('2010-05-29 01:17:35');
echo $dt->format('M j Y g:i A');

`strtotime` + `date` is the solution you'll see used the most ; but it is not the best solution : with those, you'll work on UNIX Timestamps, which means a limited range of dates *(from 1970 to 2038, if using 32 bits integers)*.

ON the other hand, using the DateTime class, there will be no limit to the range of dates you can work with.

Weig

"The range is from about 292 billion years in the past to the same in the future." - Wouldn't really call that "no limit". Oh well, +1 anyway. ;-))) – Toluidine

Humph, I was sure there was someone going to say that this is not unlimited ;-) – Weig

Answer 3

If you have a DATETIME field in your table and you only want the date field, then:

SELECT DATE_FORMAT(timestamp,'%M %D, %Y') FROM Mytable;

or:

SELECT DATE_FORMAT(timestamp,'%Y-%m-%d') FROM Mytable;

where timestamp is your column name.

Arlyne