2

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

Improve this question
2
  • Workaround: select timestampdiff(second, '1970-01-01', NOW() + INTERVAL 19 YEAR). Commented Sep 7, 2019 at 18:13
  • If you are going that far into the future, perhaps simply DATE, which runs out after "9999-12-31" (no TIME component) will work for you? See TO_DAYS() and lots of + INTERVAL ... capabilities. Commented Sep 7, 2019 at 18:27

2 Answers 2

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Do you know of any viable alternative in my case?
@Lehks Yes, you should change data type to DATETIME
It is datetime, but I used the timestamp on the client side for easier comparison. I'll try to do the comparison on the RDBMS instead.
Of course, Unix time doesn't really end on 2038. This problem is specific of systems and implementations where Unix time is stored as a signed 32-bit integer.
@Lehks - DATETIME and TIMESTAMP have virtually the same comparison capabilities. And it is easy to get from one to the other. Show us the code that is "easier".
|
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.