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"));

Wanids

A DATE type works fine here, are you running some antique MySQL version or something? Also, how exactly are you setting it? – Analects

How are you trying to store it? We can't see any sample code... – Waldheim

I Use 5.5.16 version – Wanids

can you show your table date ??@HidDencum – Titanium

Ok, this is how add date, i think problem is here: $future_date = date('Y-m-d', strtotime("+$number_years_to_add years")); – Wanids

change your column type for DATE to Varchar @HidDencum – Titanium

When you add information to your question, it's best to edit the question. – Blissful

Are on 64-bit PHP? If not, maybe you should upgrade first. – Syst

This will help: https://mcmap.net/q/151960/-year-2038-bug-what-is-it-how-to-solve-it – Melva

Answer 1

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.

Inheritrix

Answer 2

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

Blissful