Search code examples
mysqlsqlmariadbunix-timestamp

Modifying the query to solve the Problem 2038 in MariaDB

I have a SQL query:

update party set left_time=(next_dose-dose)/power,takeoff=from_unixtime(unix_timestamp()+left_time);

How can I modify it without using unix time to get the dates further than 2038-01-19?

Solution

  • If you want just the UTC time that is left_time seconds from now, just do:

    utc_timestamp() + interval left_time second

    But that's not what from_unixtime does; from_unixtime will produce a time in the session's timezone. If that is what you need, you could naively do

    current_timestamp() + interval left_time second

    but that will not produce correct results if there is a daylight savings transition upcoming, so you have to do:

    convert_tz(utc_timestamp() + interval left_time second, '+00:00', @@SESSION.time_zone)

    (An example of why you should always just store UTC times and only convert them for display.) If takeoff is a timestamp type instead of a datetime, you have to do this, since it automatically converts to/from the session timezone whenever you read/update it, though it actually stores a utc time.