Description:
Need for a deterministic function that is the inverse of unix_timestamp.
unix_timestamp(<timestamp_col>) [Taking a timestamp column and returning an epoch as seconds since 1970-01-01 00:00:00 UTC]
<=>
to_timestamp(<epoch>) [Taking an epoch and stores the correct representation in a timestamp column, like "INSERT INTO t (timestamp_col) VALUES (epoch_to_timestamp(1603590000))" or "UPDATE TABLE t SET timestamp_col = epoch_to_timestamp(1603590000) WHERE id = 1"]
In a session using a DST time_zone, we would like to be able to set a timestamp column to an epoch (seconds since 1970-01-01 00:00:00 UTC) since that is a very safe way of handling timestamps between different applications and systems, regardless of time zones. UNIX_TIMESTAMP() is a well working function for getting the epoch from a TIMESTAMP column (even without conversions, since the stored format for TIMESTAMP is indeed epoch). But when it comes to setting a TIMESTAMP column to an epoch, I can only find ways to do so safely by setting the session time_zone variable to UTC or a fixed time zone offset like '+00:00'.
This means I have to change the time_zone for the duration of the whole query, even if I am only interested in avoiding the conversion for a single column. Like when we have an ORM (Object-Relational Mapping) and existing tables/columns and it is only a single column that needs special treatment for handling time zone issues, where we do not want to affect the other columns, since we don't know what kind of expressions will be used for them and would like to introduce as little change and side effects as possible.
Question from a developer that I found no good answer to:
What is the correct way to store a numeric epoch value in a MySQL timestamp column using the ORM?
FROM_UNIXTIME does not work, setting @@timestamp and then use now() does not work either, See bug#83852.
How to repeat:
Try to insert an epoch into a TIMESTAMP column, without any unneeded and unwanted conversion by your sessions DST time_zone setting.
Suggested fix:
Since CAST(<timestamp_col> AT TIME ZONE '+00:00' AS DATETIME) was added in 8.0.22, maybe a
CAST(<number> AS TIMESTAMP)
would be a working solution, where number would be int or decimal and always be considered as seconds since 1970-01-01 00:00:00 UTC, and never go through any time zone coversions.
Otherwise to_timestamp(), epoch_to_timestamp() would be other suggestions, as well as enhancing or fixing from_unixtime() to avoid conversions via session time zone.
