Correct way to store MySQL date after year 2037

Question

I am trying to store in MySQL a date() field a successive date to the year 2037.

For example: 2065-12-01

Problem is that the field is returning: 1969-12-31

What is the correct way to record these values on DB? Should I use VARCHAR?

I compute the date field's value like this:

 $future_date = date('Y-m-d', strtotime("+$number_years_to_add years"));

A `DATE` type works fine here, are you running some antique MySQL version or something? Also, how exactly are you setting it? — Vatev, Nov 19 '15 at 11:18
How are you trying to store it? We can't see any sample code... — Jon Skeet, Nov 19 '15 at 11:19
Ok, this is how add date, i think problem is here: $future_date = date('Y-m-d', strtotime("+$number_years_to_add years")); — Hid Dencum, Nov 19 '15 at 11:25
When you add information to your question, it's best to edit the question. — O. Jones, Nov 19 '15 at 11:42

score 5 · Answer 1 · answered Nov 19 '15 at 11:28

You probably use a timestamp field to store the dates and not a datetime field.

See mysql documentation on datetime data types, specifically:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

score 3 · Answer 2 · edited May 23 '17 at 12:00

It looks to me like you're using the php date and strtotime stuff on a 32-bit platform. Those functions turn into a pumpkin, like Cinderella's carriage, at 03:14:07 UTC on 19 January 2038.

You may wish to check out the new php DateTime class, or upgrade to a 64-bit php implementation.

Also, read this. Accessing dates in PHP beyond 2038

(Thanks for catching this problem with 22 years of advance notice. Others will not.)

Correct way to store MySQL date after year 2037

2 Answers2

Linked