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.