For testing, I have first set up a simple script i.e.
Create Database 'localhost:/tmp/test.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
SET BIND OF TIME ZONE TO EXTENDED;
Create Table FB4TestData_TZ (
RowID Integer not null,
TimeCol TIME WITH TIME ZONE,
Insert into FB4TestData_TZ(RowID,TimeCol) Values(1,'11:31:05.0001 America/New_York');
Select * From FB4TestData_TZ;
Run using isql, this gives the expected output i.e. '11:31:05.0001 America/New_York'
I then investigated the same using the development version of the IBX Firebird Pascal API, focusing on decoding the value of the "TimeCol" column when the extended wire protocol is in use.
The Pascal code for decoding the buffer element looks like:
procedure DecodeTimeTZEx(bufptr: PByte;
OnDate: TDateTime; var time: TDateTime; var dstOffset: smallint;
var aTimezone: TFBTimeZoneID; var aTimezone: AnsiString);
bufLength = 128;
Hr, Mt, S, DMs: cardinal;
tzBuffer: array[ 0.. bufLength] of AnsiChar;
UtilIntf.decodeTimeTzEx(StatusIntf,PISC_TIME_TZ_EX(bufptr), @Hr, @Mt, @S, @DMs,bufLength,@tzBuffer);
time := FBEncodeTime(Hr, Mt, S, DMs);
dstOffset := PISC_TIME_TZ_EX(bufptr)^.ext_offset;
timezoneID := PISC_TIME_TZ_EX(bufptr)^.time_zone;
aTimezone := strpas(PAnsiChar(@tzBuffer));
When I run it through on the debugger, I can see that the buffer is correctly decoded by UtilIntf.decodeTimeTzEx (Hr = 11, etc). However, "dstOffset" is set to -240 and not -300 which would be
expected for the time zone at 2020/1/1.
If I add to the code:
UtilIntf.decodeTime(PISC_TIME_TZ_EX(bufptr)^.utc_time, @Hr, @Mt, @S, @DMs);
in order to directly decode the time component in GMT, this is also as expected (i.e. Hr = 16, etc.) So, how did decodeTimeTzEx correctly decode the buffer to the correct local time when the offset provided by the server was out by -60?
I then kill the server and change the system date to '2020/1/1' (at the time the test was first run the system date is 2020/6/9 i.e. when daylight savings time is in effect in America/New_York).
I now see that the buffer is also correctly decoded by UtilIntf.decodeTimeTzEx (Hr = 11, etc). However, the dstOffset is also now correctly set to -300. i.e. the dstOffset appears to be following daylight savings time rules for the current date and not for a fixed date of 2020/1/1.
The only explanation I have is that both server and client were working with the current date and that additionally the client side is ignoring the offset provided in the extended wire protocol.