Converting TIME WITH TIME ZONE to TIME (e.g. assigning CURRENT_TIME to a TIME variable or column) costs about 4 times more than the equivalent TIMESTAMP conversion with a region session time zone;

[tomaszdubiel18]

Below are the results of analysis performed by AI.

Title: Converting TIME WITH TIME ZONE to TIME (e.g. assigning CURRENT_TIME to a TIME variable or column) costs about 4 times more than the equivalent TIMESTAMP conversion with a region session time zone; TimeZoneUtil::timeTzToTime() performs three ICU calendar computations per value

Summary

Since 4.0, CURRENT_TIME returns TIME WITH TIME ZONE, so assigning it to a TIME (without time zone) variable or column requires a conversion to the session time zone, and some overhead compared to 3.0 is expected. However, with a region session time zone (here Europe/Warsaw) this conversion costs about 1.3 µs per value (1.46 - 0.14 s per 1 million on 5.0.5, compared with the same loop using LOCALTIME), while the equivalent TIMESTAMP WITH TIME ZONE to TIMESTAMP conversion of CURRENT_TIMESTAMP costs about 0.3 µs (0.45 - 0.14), about 4 times less. With an offset session time zone (+02:00) the same TIME conversion costs about 0.05-0.07 µs. The reason is that TimeZoneUtil::timeTzToTime() performs three ICU calendar computations per value, and for CURRENT_TIME the 2nd and 3rd of them cancel each other out (see Profile and Possible improvement below). 4.0.8, 5.0.5 and 6.0.0 behave the same.

This is not the case covered by #7092: its test case assigns CURRENT_TIME to a TIME WITH TIME ZONE variable, and that case is fast now (0.12-0.15 s per 1 million). It is also a different path than #7854 (CURRENT_TIMESTAMP to TIMESTAMP, fixed by #7859, which caches ICU calendars); the remaining cost of that conversion is the 0.3 µs mentioned above.

The same overhead shows up in DML: inserting 300,000 rows into a table whose TIME column has DEFAULT CURRENT_TIME takes 0.64-0.66 s on 4.0.8, 5.0.5 and 6.0.0, versus 0.23-0.27 s with DEFAULT LOCALTIME (0.21-0.22 s on 3.0.14).

Environment

• Firebird 3.0.14.33856 (release), 4.0.8.3328 (snapshot, 97f2656), 5.0.5.1903 (snapshot, 14bfcdf), 6.0.0.2197 (snapshot, e1e14ac), Linux x64, SuperServer, official tarballs.
• ICU from the OS: libicuuc/libicui18n 74.2; Firebird time zone database 2026e.
• Session time zone Europe/Warsaw (default, taken from the server environment).

Test case

set stats on;
set term ^;
-- T0: empty loop
execute block as declare n integer = 1; declare d integer; begin while (n < 1000000) do begin n = n + 1; end end^
-- T1: #7092 test case (target TIME WITH TIME ZONE)
execute block as declare n integer = 1; declare d time with time zone; begin while (n < 1000000) do begin n = n + 1; d = current_time; end end^
-- T2: target TIME
execute block as declare n integer = 1; declare d time; begin while (n < 1000000) do begin n = n + 1; d = current_time; end end^
-- T3: LOCALTIME
execute block as declare n integer = 1; declare d time; begin while (n < 1000000) do begin n = n + 1; d = localtime; end end^
-- T4: target TIMESTAMP WITH TIME ZONE
execute block as declare n integer = 1; declare d timestamp with time zone; begin while (n < 1000000) do begin n = n + 1; d = current_timestamp; end end^
-- T5: target TIMESTAMP
execute block as declare n integer = 1; declare d timestamp; begin while (n < 1000000) do begin n = n + 1; d = current_timestamp; end end^
-- T6: LOCALTIMESTAMP
execute block as declare n integer = 1; declare d timestamp; begin while (n < 1000000) do begin n = n + 1; d = localtimestamp; end end^
set term ;^

Results (seconds per 1 million iterations, median of 3 runs, versions interleaved)

Test	3.0.14	4.0.8 Europe/Warsaw	5.0.5 Europe/Warsaw	6.0.0 Europe/Warsaw	4.0.8 +02:00	5.0.5 +02:00	6.0.0 +02:00
T0 empty loop	0.07	0.09	0.11	0.10	0.09	0.11	0.10
T1 TIME WITH TIME ZONE = CURRENT_TIME (#7092)	n/a	0.12	0.15	0.14	0.12	0.14	0.13
T2 TIME = CURRENT_TIME	0.08	1.50	1.46	1.47	0.19	0.19	0.18
T3 TIME = LOCALTIME	0.08	0.12	0.14	0.13	0.12	0.14	0.13
T4 TIMESTAMP WITH TIME ZONE = CURRENT_TIMESTAMP	n/a	0.12	0.14	0.13	0.12	0.14	0.13
T5 TIMESTAMP = CURRENT_TIMESTAMP	0.09	0.45	0.45	0.42	0.13	0.17	0.15
T6 TIMESTAMP = LOCALTIMESTAMP	0.08	0.11	0.14	0.13	0.10	0.14	0.13

recreate global temporary table g_t_cur (id integer, t time default current_time) on commit delete rows;
recreate global temporary table g_t_loc (id integer, t time default localtime) on commit delete rows;
commit;
set stats on;
set term ^;
execute block as declare n integer = 0; begin while (n < 300000) do begin insert into g_t_cur (id) values (:n); n = n + 1; end end^
execute block as declare n integer = 0; begin while (n < 300000) do begin insert into g_t_loc (id) values (:n); n = n + 1; end end^
set term ;^

Profile (5.0.5.1903 with debug symbols, perf record -g during T2)

89.9%  CVT_move_common
88.7%  Firebird::TimeZoneUtil::timeTzToTime(ISC_TIME_TZ const&, Firebird::Callbacks*)
63.4%    icu_74::Calendar::computeFields
48.0%    Firebird::TimeZoneUtil::localTimeStampToUtc(ISC_TIMESTAMP_TZ&)
39.0%    Firebird::TimeZoneUtil::decodeTimeStamp(...)
20.5%    Firebird::TimeZoneUtil::timeStampTzToTimeStamp(...)
23.6%    (self) icu_74::ClockMath::floorDivide
20.8%    (self) uprv_floor_74

timeTzToTime() (src/common/TimeZoneUtil.cpp, the same in v4.0-release, v5.0-release and master):

decodeTimeStamp(tsTz, false, NO_OFFSET, &times, &fractions);      // 1st ICU computation (UTC at TIME_TZ_BASE_DATE -> local)
tsTz.utc_timestamp.timestamp_date = cb->getLocalDate();
tsTz.utc_timestamp.timestamp_time = TimeStamp::encode_time(...);
localTimeStampToUtc(tsTz);                                         // 2nd (local at current date -> UTC)
return timeStampTzToTimeStamp(tsTz, cb->getSessionTimeZone())...;  // 3rd (UTC -> session zone)

So every value goes through three full ICU calendar field computations, while TIMESTAMP WITH TIME ZONE to TIMESTAMP needs one.

Possible improvement

When the source zone of the TIME WITH TIME ZONE value equals the session time zone, which is always the case for CURRENT_TIME, the 2nd and 3rd steps convert a local time to UTC and back in the same zone on the same date. Apart from local times that fall into a DST gap, they return the result of the 1st step unchanged, so they could be skipped. Caching the converted value per request (as #7092 did for CURRENT_TIME itself) would also cover the common CURRENT_TIME case.

Context

Applications written for 3.0 commonly assign CURRENT_TIME and CURRENT_TIMESTAMP to TIME / TIMESTAMP columns and variables (defaults, triggers, procedures). Replacing them with LOCALTIME / LOCALTIMESTAMP removes the overhead, but existing code keeps working without errors and only becomes slower after the upgrade, so it is easy to miss.

[asfernandes]

Please test fix in #9194

[tomaszdubiel18]

Below are the results of analysis performed by AI.

The fix was tested with the CI artifact firebird-linux-x64 of this PR (6.0.0.2199, run 37402313173) against snapshot 6.0.0.2197 (e1e14ac), Linux x64, SuperServer, system ICU 74.2, session time zone Europe/Warsaw.
The PR build also contains the master commits after e1e14ac (#9185, #9193), which do not touch the time zone code.
No differences in results and no problems were found.

Performance

Seconds per 1 million iterations, median of 4 interleaved runs (test cases from #9191 plus T8-T12 below).

Test	6.0.0.2197	PR	2197 +02:00	PR +02:00
T0 empty loop	0.106	0.097	0.106	0.099
T1 TIME WITH TIME ZONE = CURRENT_TIME	0.137	0.128	0.142	0.129
T2 TIME = CURRENT_TIME	1.659	0.202	0.197	0.187
T3 TIME = LOCALTIME	0.142	0.128	0.139	0.131
T5 TIMESTAMP = CURRENT_TIMESTAMP	0.446	0.157	0.154	0.158
T6 TIMESTAMP = LOCALTIMESTAMP	0.139	0.125	0.140	0.131
T8 TIMESTAMP WITH TIME ZONE = <timestamp variable>	0.722	0.149
T9 dateadd(n hour to <timestamp>) (baseline for T10)	0.163	0.159
T10 TIMESTAMP = dateadd(n hour to <timestamp with time zone>), 1 million different instants over about 114 years	0.461	0.189
T11 dateadd(n second to <time with time zone>) (baseline for T12)	0.166	0.161
T12 TIME = dateadd(n second to <time with time zone>), different values	1.623	0.229

-- T8
execute block as declare n integer = 1; declare s timestamp = localtimestamp; declare d timestamp with time zone; begin while (n < 1000000) do begin n = n + 1; d = s; end end^
-- T10
execute block as declare n integer = 1; declare s timestamp with time zone = timestamp '1970-01-01 00:00:00 UTC'; declare d timestamp; begin while (n < 1000000) do begin n = n + 1; d = dateadd(n hour to s); end end^
-- T12
execute block as declare n integer = 1; declare s time with time zone = time '00:00:00 Europe/Warsaw'; declare d time; begin while (n < 1000000) do begin n = n + 1; d = dateadd(n second to s); end end^

300,000 inserts into a GTT, seconds, median of 4:

Column default	6.0.0.2197	PR
timestamp default current_timestamp	0.397	0.322
timestamp default localtimestamp	0.282	0.278
time default current_time	0.736	0.326
time default localtime	0.282	0.298

With a region session time zone the conversions now cost about the same as with an offset time zone (+02:00).

Correctness

The output of both builds was compared as text.

1. Conversions around all transitions from 1900 to 2040 (from RDB$TIME_ZONE_UTIL.TRANSITIONS) in 14 zones: Europe/Warsaw, America/New_York, Australia/Lord_Howe, America/St_Johns, Pacific/Apia, Asia/Tehran, Europe/Dublin, Africa/Casablanca, Pacific/Chatham, Antarctica/Troll, Europe/Moscow, America/Sitka, Pacific/Kiritimati, America/Sao_Paulo.
   For every transition: instants from -26 h to +26 h (hourly, every minute within ±2.5 h, every second within ±5 s), and the same displacements applied to the local wall time on both sides of the transition, which covers the DST gap and the repeated hour.
   Checked expressions, with the session time zone set to the zone: CAST(<tstz> AS TIMESTAMP), EXTRACT(TIMEZONE_HOUR / TIMEZONE_MINUTE FROM <tstz> AT LOCAL), <tstz> AT TIME ZONE 'America/New_York', CAST(<timestamp> AS TIMESTAMP WITH TIME ZONE) and the same wall time as a string literal with the region name.
   1,217,728 rows, identical.
2. TIME WITH TIME ZONE to TIME and to TIMESTAMP depend on the current date, so the server clock was set with libfaketime to 9 dates: the transition days of Europe/Warsaw, America/New_York and Australia/Lord_Howe (both directions), 1 and 2 days after the Warsaw spring transition, and an ordinary day.
   For every date: 10 source zones (including UTC, +05:30, -03:00) × 4 session zones (Europe/Warsaw, America/New_York, Australia/Lord_Howe, +02:00) × 1440 values.
   57,604 rows per date, identical.
3. CAST(CURRENT_TIME AS TIME) and CAST(CURRENT_TIMESTAMP AS TIMESTAMP) compared with LOCALTIME / LOCALTIMESTAMP in the same statement, once a second for 20 seconds across the six transitions above (including the repeated hour and the 30-minute Lord Howe change).
   Equal in every sample on both builds.

Concurrency

2 vCPU, each connection executes 2 million iterations, seconds per connection, median of 3.

Test	Connections	6.0.0.2197	PR
TIME = CURRENT_TIME	1	3.215	0.418
	2	5.184	0.409
	4	10.633	0.790
TIME = LOCALTIME (reference)	1 / 2 / 4	0.280 / 0.284 / 0.531	0.253 / 0.257 / 0.510
TIMESTAMP = dateadd(mod(n * 7919, 1000000) hour to <tstz>) (each value in a different interval)	1	1.069	0.620
	2	2.120	0.810
	4	4.328	1.637
same with <timestamp>, no conversion (reference)	1 / 2 / 4	0.406 / 0.445 / 0.795	0.399 / 0.422 / 0.794

On 2197 TIME = CURRENT_TIME does not scale even with 2 connections on 2 CPUs (5.18 s versus 3.22 s), which is consistent with a single cached UCalendar per zone and the other threads opening new calendars.
The PR build scales like LOCALTIME.
In the worst case for the new cache, where consecutive values fall into different intervals and the two recent-interval slots do not help, the conversion overhead grows from about 0.11 µs (1 connection) to about 0.19 µs per value (2 connections), still well below 2197 (0.33 and 0.84 µs).

[asfernandes]

I'm hoping it's correct. I'll not read another big-text from AI.