Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

FB4 Beta 2 may still be using the current date for TIME WITH TIME ZONE and extended wire protocol. [CORE6328] #6569

Closed
firebird-automations opened this issue Jun 9, 2020 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Tony Whyman (twhyman)

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,
Primary Key(RowID)
);

Insert into FB4TestData_TZ(RowID,TimeCol) Values(1,'11:31:05.0001 America/New_York');
commit;
Select * From FB4TestData_TZ;

Drop Database;

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);
const
bufLength = 128;
var
Hr, Mt, S, DMs: cardinal;
tzBuffer: array[ 0.. bufLength] of AnsiChar;
begin
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));
end;

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.

Commits: 85b06bc

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Can you confirm that the same thing happen with EXTRACT(TIMEZONE_HOUR FROM ...) and EXTRACT(TIMEZONE_MINUTE FROM ...) ?

@firebird-automations
Copy link
Collaborator Author

Commented by: Tony Whyman (twhyman)

I have updated the query to

Select TIMECOL,Extract(TIMEZONE_HOUR FROM TIMECOL) AS TZ_HOUR,
Extract(TIMEZONE_MINUTE FROM TIMECOL) as TZ_MINUTE from FB4TestData_TZ;

It came back with a TZ_HOUR = -4 and a TZ_MINUTE = 0

for a time with time zone set as '11:31:05.0001 America/New_York'

and a query run on 2020.7.14

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants