CODEWITHSUNDEEP
mysqlsqlmariadb
Lehks
Lehks

Reputation: 3166

MariaDB UNIX_TIMESTAMP() returns NULL when using dates in the future

I am using UNIX_TIMESTAMP(), but when I pass dates that are far in the future, it starts returning NULL at some point.

For example, the following SQL gives me a proper, non-null result: SELECT UNIX_TIMESTAMP(ADDDATE(NOW(), INTERVAL 18 YEAR)).

However, if I increase that value by one year, the returned value becomes NULL: SELECT UNIX_TIMESTAMP(ADDDATE(NOW(), INTERVAL 19 YEAR))

What is the problem here? Could it be an integer overflow? And how do I solve it?

I am using the following MariaDB version: Ver 15.1 Distrib 10.4.7-MariaDB, for Linux (x86_64) using readline 5.1

Upvotes: 2

Views: 1383

Answers (2)

Daniel J.
Daniel J.

Reputation: 366

As stated in my above comment I couldn't make it work passed the 2038 Epochalipse date limit, not even converting the field to DATETIME. There are probably other considerations affecting this eventual solution.

The only workaround that I could find was to get the date out of the DB from PHP and use the strtotime() PHP function to get the UNIX TIMESTAMP from it.

SELECT DATE_FORMAT(CONVERT(thedatefield, DATETIME),"%Y-%m-%dT%TZ") AS thedatefield

As there exist different date formats (American, English, Latin, etc...) which are incompatible and a possible source of trouble, I am using two MySQL/ MariaDB functions to flatten the output to an ISO date (YYY-MM-DDThh:mm:ss). Once the output is uniform in any system you can pass the output to the strtotime() PHP function with the confidence that it will be correctly parsed to a UNIX TIMESTAMP.

CONVERT: casts the date to a datetime DB type.

DATE_FORMAT: converts the date to ISO format (YYY-MM-DDThh:mm:ss).

You can of course remove those functions and adapt the solution to the particularities of your system to reduce the processing load. This proposed solution will work with any system date style.

In case of just one register being returned by PHP (the simplest case) I set the first dimension of the returned 2D or table array to 0

date("U",strtotime($php_array[0][thedatefield]))

The PHP date() function by virtue of the "U" flag will convert the DB output to a UNIX TIMESTAMP without the 32 bit 2038 Epochalipse limitation.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

Unix time (signed 32-bit) ends on 03:14:07 Tuesday, 19 January 2038 UTC

Related: Year_2038_problem

UNIX_TIMESTAMP:

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. Use DATETIME as a storage type if you require dates beyond this.

SELECT ADDDATE(NOW(), INTERVAL 19 YEAR)  -- DATETIME
-- 2038-09-07 18:42:39

Upvotes: 4

Related Questions