Mysql Question posted in
The official MySQL documentation can be found here.

mysql virtual column incoherence wrt from_unixtime()

Got a table with a virtual column using from_unixtime():

CREATE TABLE aa (
 id int NOT NULL AUTO_INCREMENT,
 epochmillis bigint,
 session_dt DATETIME(3) generated always as (from_unixtime(epochmillis/1000)),
 -- utc DATETIME(3) generated always as (convert_tz(from_unixtime(epochmillis/1000), @@time_zone, 'UTC')),
PRIMARY KEY (`id`));

I can’t define an expression that uses variables (commented out), but from_unixtime() is using @@time_zone internally! That’s unfair! LOL. Basically, this table returns session_dt according to the session timezone, which is more or less wrong depending on your opinion on requiring a session timezone, but just the same ‘violation’ as the variable I’m trying to use.

Ok, maybe you guessed what I’m doing: trying to get a stable UTC datetime(3) from an epochtimemillis column going well beyond 2038 (up to 3001 I think)(for the few date operations that could be easier on a datetime).

Just trying to get the best of both worlds (trivial epoch time, convenient zone-free datetime). In particular, I want to avoid the typical saving datetime in a writer’s timezone and recuperating it in a distinct reader’s timezone. By saving forcibly as epochtime, and with a generated datetime column that would give UTC, there would be no way to circumvent storing the proper instant.

TO BE CLEAR: I wish I could prevent from_unixtime() to choose the session’s timezone and prescribe the timezone on the statement. All attempts at convert_tz are not desirable and only trying to undo that hidden choice.

What can I do to achieve my goal?

Reference fiddle:
https://dbfiddle.uk/MqZeNEMP

Tags:

2

Answers


  1. 0

    Converting time zones is deceptively tricky. One issue is if you store a date in the future, because it’s impossible to know if some future policy will change a time zone, but you can only convert the time zone according to current policy. See https://codeopinion.com/just-store-utc-not-so-fast-handling-time-zones-is-complicated/ for more on this.

    The only way you can make it clear in your database is store both the local time and UTC time as real columns (i.e. don’t store the UTC time as a generated column), and also store the time zone of the local time, and the version of the timezone database at the time you last converted the local time to the UTC time.

    CREATE TABLE aa (
 id int NOT NULL AUTO_INCREMENT,
 epochmillis bigint,
 session_dt_local DATETIME(3),
 session_dt_utc DATETIME(3),
 local_tz_name VARCHAR(64),    -- e.g. "America/Toronto"
 tzdb_version VARCHAR(16),     -- e.g. "2022a"
 PRIMARY KEY (`id`)
);

    That way if the timezone database changes for that locale at some time in the future, whoever is maintaining this database at that time has enough information to recalculate the UTC time according to the new timezone definition.

    You can possibly fill in the session_dt_local as a generated column, since it can be done without referencing the timezone variable.

    But you can’t convert to UTC as a generated column. You must use a trigger for that. Since you have to write a trigger anyway, you might as well do both the local and UTC datetime calculations in one place. But that’s up to you.

    Login or Signup to reply.
  2. 0

    You can convert from epoch milliseconds to a UTC time with addition:

    session_dt DATETIME(3) generated always as ('1970-01-01' + interval epochmillis*1000 microsecond),

    That said, you really should just store session_dt in the first place, not use a generated column. There’s no good reason for storing dates as integers. If you need the epoch milliseconds as a query result, you can get it from session_dt:

    floor(timestampdiff(microsecond,'1970-01-01',session_dt)/1000)

    Note that utc_timestamp(3) can be used to populate session_dt with the current utc time. You should avoid functions like current_timestamp, from_unixtime, unix_timestamp that are affected by the session timezone. You may have to figure out how to get your client to expect datetimes to be in utc, but that’s worth doing anyway.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search