convert mysql timestamp into actual date and time?

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!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

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

Method 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.

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0