CODEWITHSUNDEEP
phpmysqldatedatetime
Kenny Johnson
Kenny Johnson

Reputation: 784

PHP/MySQL - Best way to work with dates in DD MMM YYYY format?

I need to display dates in the DD MMM YYYY format on the website I'm working on. My first thought was to store the dates in a DATE type in MySQL, then convert to the proper format using PHP. However, this is just returning 01 Jan 1970 - not the actual date of 04 May 1891.

I found a few other places where people have asked similar questions and the answer was always something like SELECT date_format(dt, '%d/%m/%Y') which was giving me 04/05/1891. I want the month to be 3 characters (May, Jun, Jul, Aug, etc).

I thought about storing the dates as a VARCHAR but that seems like it would be bad practice. It looks like I will have to store the date as a string and will not be able to use the PHP date() function because it cannot handle dates prior to 1970.

I may want to do things like calculate ages which sounds painful when dealing with strings... I am wondering if anyone has a more elegant solution?

Upvotes: 1

Views: 2879

Answers (2)

user2851392
user2851392

Reputation:

You can use DATE to save your dates in your db.

DATE can store values from '1000-01-01' to '9999-12-31' (source)

PHP's old date functions (i.e. date(), strtotime()) are on 32 bits and therefore work only with UNIX timestamps (they're limited to the 1970-2038 range).

With PHP 5's new class DateTime you can do for example:

$date = new DateTime("1780-06-01");
echo $date->format('Y-m-d H:i:s');

EDIT: To compute the difference between two dates (as stated in your question), an easy way is:

$datetime1 = new DateTime('2009-10-11');
$datetime2 = new DateTime('2009-10-13');
$interval = $datetime1->diff($datetime2);
echo $interval->format('%Y years');

Upvotes: 4

DevlshOne
DevlshOne

Reputation: 8457

I always use a constants file....

define('OUTDATE','%d %b %Y');
define('INDATE', '%Y-%m-%d');

Then (as noted in the comments), manipulate the strings using the constants.

$sql = "SELECT DATE_FORMAT(birthday, ".OUTDATE.") FROM table1";

Upvotes: 0

Related Questions