Mixing operator with unix_timestamp yields unexpected null

Question

My table has some TIMESTAMP columns, for which I am running a query to get the most recent one as a Unix timestamp. Suppose all the columns are zeroed (they are default '0'), i.e. I didn't set the value yet.

• If I select each column with unix_timestamp(col), it will return 0 for each column:

select unix_timestamp(`last_ip_ok`), unix_timestamp(`last_ip_error`), unix_timestamp(`last_ownership_ok`), unix_timestamp(`last_ownership_error`) from `websites`;
+------------------------------+---------------------------------+-------------------------------------+----------------------------------------+
| unix_timestamp(`last_ip_ok`) | unix_timestamp(`last_ip_error`) | unix_timestamp(`last_ownership_ok`) | unix_timestamp(`last_ownership_error`) |
+------------------------------+---------------------------------+-------------------------------------+----------------------------------------+
| 0                            | 0                               | 0                                   | 0                                      |
+------------------------------+---------------------------------+-------------------------------------+----------------------------------------+

• If I select the greatest of all columns, it will return the zero timestamp, that is, 0000-00-00 00:00:00:

select greatest(`last_ip_ok`, `last_ip_error`, `last_ownership_ok`, `last_ownership_error`) from `websites`;
+--------------------------------------------------------------------------------------+
| greatest(`last_ip_ok`, `last_ip_error`, `last_ownership_ok`, `last_ownership_error`) |
+--------------------------------------------------------------------------------------+
| 0000-00-00 00:00:00                                                                  |
+--------------------------------------------------------------------------------------+

• However, if I combine the functions and run unix_timestamp(greatest(cols...)), it just returns null:

select unix_timestamp(greatest(`last_ip_ok`, `last_ip_error`, `last_ownership_ok`, `last_ownership_error`)) from `websites`;
+------------------------------------------------------------------------------------------------------+
| unix_timestamp(greatest(`last_ip_ok`, `last_ip_error`, `last_ownership_ok`, `last_ownership_error`)) |
+------------------------------------------------------------------------------------------------------+
| <null>                                                                                               |
+------------------------------------------------------------------------------------------------------+

This is unexpected, I expected the return value to be 0 since it should be equivalent to unix_timestamp('0000-00-00 00:00:00'), which is the return value of greatest(cols...). Is this correct, or is it possibly a bug?

Answer 1

Actually if you do:

 SELECT UNIX_TIMESTAMP('0000-00-00 00:00:00');

you will get NULL because it is not a valid date for the UNIX_TIMESTAMP range.

UNIX_TIMESTAMP(datetime) returns the number of seconds between 1970-01-01 00:00:00 UTC and datetime. Thus datetime needs to be a VALID date and time between 1970-01-01 00:00:00 and 2038-01-19 03:14:07. If it is not valid or not in that range, UNIX_TIMESTAMP will return NULL.

I am puzzled why you are getting "0" in your first query. When I test (even with SQL mode allowing zero dates)

SELECT UNIX_TIMESTAMP(0);

I get an expected NULL result. We need to have the example data dump to investigate further.

Answer 2

I'm a little confused. When I run:

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

I get NULL, not 0. Here is a db<>fiddle.

I can't think of a reason why your first query would return 0, rather than