Issue Details (XML | Word | Printable)

Key: CORE-6328
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Tony Whyman
Votes: 0
Watchers: 5
Operations

If you were logged in you would be able to see more operations.
Firebird Core

FB4 Beta 2 may still be using the current date for TIME WITH TIME ZONE and extended wire protocol.

Created: 09/Jun/20 08:55 AM   Updated: 14/Jul/20 07:10 PM
Component/s: API / Client Library
Affects Version/s: 4.0 Beta 2
Fix Version/s: 4.0 RC 1

Environment:
Linux Mint 19.3, Firebird Beta 2
Firebird/Linux/AMD/Intel/x64 (access method), version "LI-T4.0.0.2006 Firebird 4.0 Beta 2"

QA Status: Done successfully


 Description  « Hide
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.




 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 13/Jul/20 04:27 PM
Can you confirm that the same thing happen with EXTRACT(TIMEZONE_HOUR FROM ...) and EXTRACT(TIMEZONE_MINUTE FROM ...) ?

Tony Whyman added a comment - 14/Jul/20 09:06 AM
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