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?

CodePudding user response：

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.