PostgreSQL timestamp with timezone issue

[MJFShark]

The changes to the timezone stuff for Pg are great! A few issues:

in TZAbstractPostgreSQLPreparedStatementV3.GetRawEncodedSQL

it does:

Code: Select all

        if (FTokenMatchIndex = -1) and (FPQResultFormat = ParamFormatBin) then

However FPQResultFormat hasn't been set yet and since it's not initialized it is always zero (ParamFormatStr.) My quick fix is to just remove that check (which isn't optimal in ParamFormatStr mode obviously.)

Also the "set" command for time zones allows the syntax:

SET TIMEZONE = 'UTC';
(besides the normal SET TIME ZONE syntax)

So I added cTimeZone: PChar = 'TIMEZONE'; to the constants and check for it before the check for cTime. Here's my code:

Code: Select all

            if (NextToken.TokenType = ttWord) and (NextToken.L = 8) and SameText(NextToken.P, cTimeZone, 8) and (Tokens.Count > J+1) then begin
              FDoUpdateTimestampOffet := True;
            end
            else    // next line already exists
            if (NextToken.TokenType = ttWord) and (NextToken.L = 4) and SameText(NextToken.P, cTime, 4) and (Tokens.Count > J+1) then begin

Thanks!

-Mark

[Fr0sT]

General note: storing timestamps with offsets is just like storing strings in different codepages. It's even worse because offsets change.
You pre-calculate current offset and a second later winter=>summer time happens. Or offset changes. How about calculating correct offset for a time in the past if there were two offset changes and DST change cancellation since then?
So there are only two reliable options - use GMT0 everywhere even for displaying and use OS/ICU facilities for getting offsets (still need to be maintained for recent changes)