Working with TimeStampWithTimeZone field types

[Roger Cigol 88]

I am using C++ Builder (RAD Studio 12.0) compiling to a Win64 VCL project working with PostgreSQL 13 database.

RAD Studio 12.0 includes the bug fix of RSP-28292 - use of SQLTimeStampOffset fields in FireDAC.

I have fields in my postgreSQL tables that are type TimeStampWithTimeZone. When viewed using PgAdmin4 they display values such as

 

2024-01-15 15:05:12+00   (for winter in the UK = UTC)

or

2016-06-09 15:13:03+01 (for summer in the UK = BTS - one hour ahead of UTC).

 

I use fireDAC TFDQuery and obtain datasets with these fields in. If I use 

 

// ToBeRead is the TField pointer within the dataset (typically obtained using FieldByName())
TSQLTimeStampOffset UTC_O = ToBeRead->AsSQLTimeStampOffset;

Then I get a value in UTC_0 which has a time offset figure of zero and the hours compensated for. Using the above two examples this

would mean UTC_0 would be equal to

 

{2024, 01, 15, 15, 05, 12, 0, 0, 0}

 

or

 

{2016, 06, 09, 14, 13, 03, 0, 0, 0}    // note: time offset is returned as zero and the hours value has been decremented by 1

 

This is not the behaviour I expected. I thought I would get the value as local time and with the time offset value set to the

same value as displayed by pgAdmin4, ie for the summer case, I would expect

{2016, 06, 09, 15, 13, 03, 0, 1, 0}   // result in BST and time offset = 1 hour

 

Users of my software want to see the time values displayed in local time (of course) so I have to convert this UTC value to

BST for date/time values in the summer. 

 

RAD Studio 12.0 doesn't seem to implement functions UTCToLoca() or LocalToUTC() for arguments of type TSQLTimeStampOffset.

It DOES implement these functions for type TSQLTimeStamp. So I am forced to first convert my TSQLTimeStampOffset to

a TSQLTimeStamp value and then convert it to local time. I use this code....

 

TDateTime GetPgSQLDataSetFieldAsLocalTime(TField *ToBeRead)
{
   TDateTime LocalDateTime;
   if (ToBeRead) {
      // Reading AsSQLTimeStampOffset seems to return UTC value
      // (ie TimeZone is Zero and hours (+days etc) have been compensated correctly)
      // This is not ideal !
      TSQLTimeStampOffset UTC_O = ToBeRead->AsSQLTimeStampOffset;
      TSQLTimeStamp UTC;
      UTC.Year = UTC_O.Year;
      UTC.Month = UTC_O.Month;
      UTC.Day = UTC_O.Day;
      UTC.Hour = UTC_O.Hour;
      UTC.Minute = UTC_O.Minute;
      UTC.Second = UTC_O.Second;
      UTC.Fractions = UTC_O.Fractions;
      TSQLTimeStamp Local = UTCToLocal(UTC);
      LocalDateTime = SQLTimeStampToDateTime(Local);
   }
   return LocalDateTime;
}

This all works but it all has the feel of being a "work around".

 

To update the field from a local time I am now using this code.

 

void UpdatePgSQLDataSetFieldFromLocalTime(TField *ToBeUpdated, TDateTime LocalDateTime)
{
   if (ToBeUpdated) {
      TSQLTimeStamp Local = DateTimeToSQLTimeStamp(LocalDateTime);
      TSQLTimeStamp UTC = LocalToUTC(Local);
      TSQLTimeStampOffset UTC_O;
      UTC_O.Year = UTC.Year;
      UTC_O.Month = UTC.Month;
      UTC_O.Day = UTC.Day;
      UTC_O.Hour = UTC.Hour;
      UTC_O.Minute = UTC.Minute;
      UTC_O.Second = UTC.Second;
      UTC_O.Fractions = UTC.Fractions;
      UTC_O.TimeZoneHour = 0;
      UTC_O.TimeZoneMinute = 0;
      ToBeUpdated->AsSQLTimeStampOffset = UTC_O;
   }
}

Again, this all works. But it all seems fairly "hard work" to code.

 

I post the code here in case it helps anyone out.

 

I am interested to know if anyone has a better way of working with TimeStampWithTimeZone field types and local time values.

[Roger Cigol 88]

I thought I would add some code with "what doesn't work" and how to use the above two functions to make it work.

Assume you have a postgreSQL database connected and that you have a query that returns a record that contains a date/time value and that the database stores this value as a "time stamp with time zone" format (PostgreSQL users can call this timestamptz).

I would expect the following code to work - but after you run it AllOkA is true (showing it works for UTC local time) but AllOkB is false (showing it falls over for BST (British Summer Time)).

         TFDQuery *FDQuery = SampleFDQuery;  // a query which returns a record with a timestampwithoffset field
         FDQuery->Close();
         // work with the oldest record (any one would do)
         FDQuery->ParamByName(SSQP_PrimaryKey)->AsLargeInt = 1;
         FDQuery->OpenOrExecute();
         FDQuery->First(); // should only be one entry
         TField *Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
         // try a winter time (January)  (this is UTC - works without special function calls
         TDateTime WinterLocalDateTime{2020, 1, 17, 9, 8, 7, 0};
         FDQuery->Edit();
         Field->AsDateTime = WinterLocalDateTime;   //=== 
         FDQuery->Post();
         FDQuery->Close();
         FDQuery->OpenOrExecute();
         Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
         TDateTime Check = Field->AsDateTime;  //=== 
         FDQuery->Close();
         AllOkA = (WinterLocalDateTime == Check);
         if (AllOkA) {
            FDQuery->OpenOrExecute();
            Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
            // try a summer time (June) (BST - one hour ahead of UTC)
            TDateTime SummerLocalDateTime{2020, 6, 17, 9, 8, 7, 0};
            FDQuery->Edit();
            Field->AsDateTime = SummerLocalDateTime;  //=== 
            FDQuery->Post();
            FDQuery->Close();
            FDQuery->OpenOrExecute();
            Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
            Check = Field->AsDateTime;  //=== 
            FDQuery->Close();
            AllOkB = (SummerLocalDateTime == Check);
         }

If I replace the lines with comments //=== with calls to the functions given in my previous posting I get code that works for both winter and BST summer times (ie both AllOkA and AllOkB are true at the end of the code).

         TFDQuery *FDQuery = SampleFDQuery;  // a query which returns a record with a timestampwithoffset field
         FDQuery->Close();
         // work with the oldest record (any one would do)
         FDQuery->ParamByName(SSQP_PrimaryKey)->AsLargeInt = 1;
         FDQuery->OpenOrExecute();
         FDQuery->First(); // should only be one entry
         TField *Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
         // try a winter time (January)  (this is UTC - works without special function calls
         TDateTime WinterLocalDateTime{2020, 1, 17, 9, 8, 7, 0};
         FDQuery->Edit();
         UpdatePgSQLDataSetFieldIfNotDefaultDate(Field, WinterLocalDateTime); //===
          FDQuery->Post();
         FDQuery->Close();
         FDQuery->OpenOrExecute();
         Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
         TDateTime Check = GetPgSQLDataSetFieldAsLocalTime(Field); //===
         FDQuery->Close();
         AllOkA = (WinterLocalDateTime == Check);
         if (AllOkA) {
            FDQuery->OpenOrExecute();
            Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
            // try a summer time (June) (BST - one hour ahead of UTC)
            TDateTime SummerLocalDateTime{2020, 6, 17, 9, 8, 7, 0};
            FDQuery->Edit();
            UpdatePgSQLDataSetFieldIfNotDefaultDate(Field, SummerLocalDateTime);//===
            FDQuery->Post();
            FDQuery->Close();
            FDQuery->OpenOrExecute();
            Field = FDQuery->FieldByName(PG_Sensors_TimeDate);
            Check = GetPgSQLDataSetFieldAsLocalTime(Field); //===
            FDQuery->Close();
            AllOkB = (SummerLocalDateTime == Check);
         }

I do not have a copy of Microsoft SQLServer but I would think it very likely that the same problem exists with this too.  (but am wise enough to wait for someone else to verity this !).

[Roger Cigol 88]

Just realised I missed out the (trivial) definitions of AllOkA and AllOkB at the top of the code: For completeness I add:

   bool AllOkA = false;
   bool AllOkB = false;