MySQL mode : `FROM_UNIXTIME` does not honour session timestamp when it should

[bheading]

The H2 MySQL emulation of FROM_UNIXTIME() suggests it was derived from the MySQL 5.1 reference manual documented here function as follows :

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

The H2 implementation is as follows :

    public static String fromUnixTime(int seconds, String format) {
        format = convertToSimpleDateFormat(format);
        SimpleDateFormat formatter = new SimpleDateFormat(format, Locale.ENGLISH);
        return formatter.format(new Date(seconds * 1_000L));
    }

SimpleDateFormat will use the current system timezone, as configured in the JVM, to generate the output. It will not use the session timezone.

The opposite function, UNIX_TIMESTAMP(), does honour the session timezone in the H2 implementation.

I was unable to obtain a version of MySQL 5.1 to verify what its behaviour was. I did manage to run a Docker container of MySQL 5.5.61. Here is the behaviour of the two functions :

mysql> SET @@session.time_zone = "America/Mexico_City";
Query OK, 0 rows affected (0.00 sec)

mysql> select FROM_UNIXTIME(1756684800), UNIX_TIMESTAMP('2025-08-31 19:00:00');
+---------------------------+---------------------------------------+
| FROM_UNIXTIME(1756684800) | UNIX_TIMESTAMP('2025-08-31 19:00:00') |
+---------------------------+---------------------------------------+
| 2025-08-31 19:00:00       |                            1756684800 |
+---------------------------+---------------------------------------+
1 row in set (0.00 sec)

You can see on MySQL that the UTC epoch time 1756684800 (which is 2025-09-01 00:00:00 UTC) is being translated to the Mexico City time of 2025-08-31 19:00:00 CST, which is five hours behind UTC during daylight savings time. The same time string is translated back into the same value in epoch seconds.

However, on H2 (using the web console with the URL : jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;MODE=MYSQL;TIME ZONE=America/Mexico_City) :

select FROM_UNIXTIME(1756684800), UNIX_TIMESTAMP('2025-08-31 19:00:00');
FROM_UNIXTIME(1756684800)  	UNIX_TIMESTAMP('2025-08-31 19:00:00')  
2025-09-01 01:00:00	1756684800
(1 row, 3 ms)

The FROM_UNIXTIME value returned, 2025-09-1 01:00:00 is the time on my local machine which is BST.

I wondered if perhaps there was an ambiguity in the documentation of these functions. The MySQL documentation of UNIX_TIMESTAMP in 5.1 :

The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in Section 9.6, “MySQL Server Time Zone Support”.

The linked page explains the behaviour of the system and session timezones.

Both descriptions refer to "current time zone". It seems reasonable to believe that this was intended to refer to the session timezone; in later versions of MySQL the documentation has been clarified.

Expected behaviour

H2 should honour the session timezone when executing the FROM_UNIXTIME() function.

[bheading]

Added a draft PR - comments welcome.