Search the Community

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.