CODEWITHSUNDEEP
mysqlunix-timestamp
Emiliano Gustavo Nuñez
Emiliano Gustavo Nuñez

Reputation: 815

mysql strange situation with unix_timestamp

Simple question:

I have 2 Mysql servers and I run this query in both:

select unix_timestamp('0000-00-00 00:00:00');

Results:

On server 1:

mysql> select unix_timestamp('0000-00-00 00:00:00');
+---------------------------------------+
| unix_timestamp('0000-00-00 00:00:00') |
+---------------------------------------+
|                                  NULL |
+---------------------------------------+

On server 2:

mysql> select unix_timestamp('0000-00-00 00:00:00');
+---------------------------------------+
| unix_timestamp('0000-00-00 00:00:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

I need the 0 in both cases.

Why? Any suggestions?

Thanks!

Upvotes: 3

Views: 783

Answers (2)

Emiliano Gustavo Nuñez
Emiliano Gustavo Nuñez

Reputation: 815

Error handling

In MySQL and MariaDB before 5.3 wrong arguments to UNIX_TIMESTAMP() returned 0. Starting fromMariaDB 5.3 we return NULL for wrong arguments to UNIX_TIMESTAMP().

Upvotes: 0

Tomas Creemers
Tomas Creemers

Reputation: 2715

I am going to assume that your two servers do not run the same MySQL version.

MySQL returns 0 when you pass a timestamp to UNIX_TIMESTAMP() that is out of range.

MariaDB (the MySQL fork that is currently used by some Linux distributions as the default) returns NULL in the same situations.

A timestamp is out of range for UNIX time if it is before 1970-01-01 00:00:00 or after 2038-01-19 03:14:08. This is because it is stored as a 32-bit unsigned integer. 1970-01-01 00:00:00 is 0 and since it is unsigned it cannot express anything before that. On 2038-01-19 03:14:08 the integer will reach its maximum value and anything after that also cannot be expressed as a UNIX timestamp. (A solution that is already in place on some systems is to use a 64-bit integer instead, giving us several hundred thousand years extra.)

See the MySQL documentation for UNIX_TIMESTAMP() and a MariaDB-related article about this problem.

Upvotes: 3

Related Questions