year 2038 issue - 64 bit (Linux OS, php, mysql)

Question

select unix_timestamp('2038-01-19') returns 2147472000

while select unix_timestamp('2038-01-20') returns 0

I have checked out the year 2038 problem.

My linux OS is 64 bit and installed mysql version is also 64 bits. What is the solution to this problem now?

mysql --version returns mysql Ver 14.14 Distrib 5.5.47, for Linux (x86_64) using readline 5.1
Php is 64 bit too.

Tried BigInt too, didn't work (returns the same thing).

`select unix_timestamp('2038-01-19') returns 2147472000` `while select unix_timestamp('2038-01-19') returns 0` can you tell what is difference in the two query? — Alive to die - Anant, Apr 22 '16 at 12:13
`UNIX_TIMESTAMP()` expects a date. If you feed it with strings you rely on implicit type casts. You'll probably get a more coherent solution if you first clarify on what precise date type you want to operate. — Álvaro González, Apr 22 '16 at 12:21
@ÁlvaroGonzález nope, it returns zero if I don't pass string to it. — Danyal Sandeelo, Apr 22 '16 at 12:27
"Tried BigInt too, didn't work (returns the same thing)." -- What exactly did you try, in the context of the original question? — apokryfos, Apr 22 '16 at 12:34
Not sure I follow you now. It returns zero because the output type is probably a 32-bit integer, not because you pass a string. Just pass a date below 2038 and you'll see. It's the same reason why `'3'*'4'` works: implicit casts. — Álvaro González, Apr 22 '16 at 12:36
select unix_timestamp(2038-01-19) returned 0, select unix_timestamp(2038-01-20) returned zero.. @apokryfos — Danyal Sandeelo, Apr 22 '16 at 12:39
@ÁlvaroGonzález it returns 0 for this too ->select unix_timestamp(2011-01-20) — Danyal Sandeelo, Apr 22 '16 at 12:42
the param in unix_timestamp needs to be passed in quotes but yes that should be a valid date format @ÁlvaroGonzález — Danyal Sandeelo, Apr 22 '16 at 12:42
Honestly, I'd say you have to let your users know that they have to accept the limitation of not being able to schedule (?) anything more than 22 years in the future (give or take) until MySQL gets up to speed and starts using 64-bit timestamps. — apokryfos, Apr 22 '16 at 12:54

score 6 · Accepted Answer · edited May 23 '17 at 12:31

6

Simply put, for MySQL, store dates as DATETIME rather than TIMESTAMP.

TIMESTAMP is 4 bytes, so there is no physical room to store more seconds than since 1970-1-1 to 2038-01-19...

DATETIME, instead, has a range of 1000-1-1 to 9999-12-31...

See also this complete question/answer: PHP & mySQL: Year 2038 Bug: What is it? How to solve it?

UPDATE: One possible alternative I see, if you CAN'T change your fields types, is to interpet your timestamps differently...
I mean: if the first event your application will keep track of is - say - 2000-1-1, you could implement a filter on backend (or in a stored procedure inside the database), to add (2000-1-1 - 1970-1-1) seconds to your timestamps when reading, and subtract the same amount when reading... This should give you 30 more years of 'survival'...

edited May 23 '17 at 12:31

Community

1
1

answered Apr 22 '16 at 12:16

MarcoS

17,323
24
96
174

We have kept it as INT in our database, don't want to redesign it. – Danyal Sandeelo Apr 22 '16 at 12:17
3

@DanyalSandeelo: And I don't want to pay my rent next month. I guess we'll both have to be unhappy. *shrug* – Ignacio Vazquez-Abrams Apr 22 '16 at 12:19
@IgnacioVazquez-Abrams The application is in UAT. All of the tables store date in INT format, I can't risk the deadline. Though I know, datetime would solve the issue – Danyal Sandeelo Apr 22 '16 at 12:21
1

:-))) A MySQL `INT` is 4 bytes, so you physically CAN'T store more than 2,147,483,648 seconds, sorry... – MarcoS Apr 22 '16 at 12:21
gives the same output @MarcoS – Danyal Sandeelo Apr 22 '16 at 12:31
1

`UNIX_TIMESTAMP` expects no parameter or one `date` parameter... See https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp – MarcoS Apr 22 '16 at 12:36

Álvaro González · Answer 2 · 2016-04-22T12:55:02.983

MySQL documentation is in general extremely vague about the data types returned by functions and UNIX_TIMESTAMP() is not an exception. Unless we check the source code I think we can only make an educated guess.

At Date and Time Type Overview we can read that the TIMESTAMP data type itself has a documented range that doesn't depend on the server architecture:

The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value.

Even if we make sure we pass a proper date type:

mysql> select
    ->  str_to_date('2038-01-20', '%Y-%m-%d'),
    ->  unix_timestamp(str_to_date('2038-01-20', '%Y-%m-%d'));
+---------------------------------------+-------------------------------------------------------+
| str_to_date('2038-01-20', '%Y-%m-%d') | unix_timestamp(str_to_date('2038-01-20', '%Y-%m-%d')) |
+---------------------------------------+-------------------------------------------------------+
| 2038-01-20                            |                                                     0 |
+---------------------------------------+-------------------------------------------------------+
1 row in set (0.01 sec)

... we still get 0, the silly function flag for errors:

If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0.

So it's kind of safe to assume that UNIX_TIMESTAMP() returns a value of TIMESTAMP type thus 2038+ is not supported.

In short: you'll have to calculate timestamps somewhere else (i.e., your client code). Since there's a PHP tag:

$t = new DateTime('2038-01-20', new DateTimeZone('UTC'));
var_dump( $t->format('U') );

string(10) "2147558400"

P.S. MariaDB, the MySQL fork, has the same restriction but it documents it better:

Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07. This is due to the underlying 32-bit limitation. Using the function on a date beyond this will result in NULL being returned.

We'd need to know the time zone and where/how you calculate that figure. Are you referring to my PHP snippet? :-? — Álvaro González, Apr 22 '16 at 12:56
I am executing this sql query in mysql client. I have bought Amazon's instance with linux on it. — Danyal Sandeelo, Apr 22 '16 at 14:23
If you run `SELECT unix_timestamp(str_to_date('2038-01-19', '%Y-%m-%d'))` in your Amazon MySQL service and you get `2147472000` that means your server time zone is UTC. Is that a problem of some kind? Do you have a followup question or something? — Álvaro González, Apr 23 '16 at 14:08

year 2038 issue - 64 bit (Linux OS, php, mysql)

2 Answers2

Linked