PostgreSQL timestamp with timezone issue

[MJFShark]

Hi All!

I'm fairly new to PostgreSQL and I noticed that using the Zeoslib PostgreSQL protocol querying CURRENT_TIMESTAMP always gives an unadjusted value (it seems to return UTC time which is what PostgreSQL stores for TimestampTz types.) I think that's incorrect (or perhaps I'm doing something wrong or missing a setting.) I installed the PostgreSQL ODBC driver and using that with the Zeoslib ODBC protocol I get back the correct adjusted time. So for example:

PostgreSQL Protocol:
SQL> select current_timestamp;
Result: 2021-03-04 04:35:47.120178 (my local and server time was 23:35 so this appears to be UTC time, 5 hours ahead of my Tz)
SQL> select to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss TZH')
Result: 2021-03-03 23:35:47 -05 (this is what I'd expect, though I'm formatting it to show the Tz.)

ODBC Protocol using the PostgreSQL ODBC driver:
SQL> select current_timestamp;
Result: 2021-03-03 23:33:20 (This is correct and what I'd expect above)
SQL> select to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss TZH')
Result: 2021-03-03 23:33:20 -05

Any thoughts or suggestions appreciated!

-Mark

[marsupilami]

Hello Mark,

just guessing: maybe libpq doesn't determine the client time zone automatically but requires the application to set it. Assuming that Zeos doesn't do that automatically, we most probably get connected using UTC/GMT. I assume that the ODBC driver automatically determines the client time zone and sets it correctly when connecting.
Another guess: I seem to remember that we set PostgreSQL connection options using one long string. We set the SSL properties there too. I would not be surprised if there was an option there to specify the client time zone too.

Best regards,

Jan

[marsupilami]

Another guess: I seem to remember that we set PostgreSQL connection options using one long string. We set the SSL properties there too. I would not be surprised if there was an option there to specify the client time zone too.

I stand corrected by the PostgreSQL documentation on Date/Time Types:

• The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.
• The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

So you would have to send your own "SET TIME ZONE" command to the Server. I am pretty sure that Zeos doesn't set it automatically. Maybe we have to think about a general option for that kind of things, like "client time zone=" or something like that...

[MJFShark]

Thanks Jan. It's pretty interesting. I'm aware of the various set time zone settings and have included that in my testing. As you mention the time zone defaults to the server tz, but I'm seeing all timestamptz datatypes are returned in UTC regardless of other settings (unless they are converted to timestamp by "at timezone" or such.) It definitely seems strange as doing something like "extract hour" from the timestamptz gets the adjusted value, not the UTC one even though the UTC one is what's retrieved by PQGetValue.

Interestingly, If you set the DSProps_BinaryWireResultMode property to string instead of binary (the default) the results for timestamptz are correct and adjusted to the current time zone setting, but as the string includes the timezone part (ex '2021-03-05 15:31:32.505738-05') and since the .z part is variable depending on scale requested, it completely messes up converting it to a zeoslib timestamp format. As a result the DSProps_BinaryWireResultMode=string property doesn't work for timestamptz.

[updated to remove incorrect info on my part]

-Mark

[MJFShark]

My testing shows that if the postgresql protocol is set for binary return values, that timestamptz fields are always returned as UTC. This appears to be contradictory to the documentation which states that they are output in the current sessions timezone setting. If the protocol is set for string return values then timestamptz are returned converted to the correct timezone along with an offset indicator. This means that in its default mode the PostgreSQL protocol returns (in my opinion) incorrect results for all timestamptz fields. Oddly enough, timetz fields are returned adjusted correctly even in binary mode. I've looked and I can't find anything in the Pg documentation that describes this behavior or any way to change it (which would be ideal.)

Solutions:

#1 Use the string output mode of the pg client (which would need to be fixed a bit as the string > timestamp conversion routines don't convert it correctly under some conditions.) It also relies on the sessions datestyle setting. So this option isn't ideal, but may be the easiest solution. Note the easiest way to test this is using the protocol option BinaryWireResultMode=False.

#2 Use the binary output mode, but fix the timestamps to the "correct" timezone. This is a bit complex because the correct timezone isn't the local timezone, it's whatever is set in the sessions timezone setting. OR, find out if it's possible to get the Pg client to do the conversion (I haven't found anything, but that would obviously be the best.)

Would love to hear any thoughts on this. Thanks!

-Mark

[EgonHugeist]

Hi Mark,

what the hell..! First of all: There are no tests for this situation available in our test-suite. If they would exist i wouldn't set the binary-wire as default for PG.

Yes you are right the posgres docs descibe this behavior complete different: https://www.postgresql.org/docs/9.1/dat ... etime.html see section: 8.5.1.3. Time Stamps. Thus i didn't care about.

According your proposals:
#1: not a option for me. Users may have there reason doing that. That's why it's optional.
#2: Is more complex but can be resolved! Honestly all the servers using Lib ICU http://userguide.icu-project.org/. Timezone offsets are compliated because the UTC offset change from time to time depending to "Winter"/"Sommer" time or if a country did splitt into several more etc. In some countries the "Winter/Sommer" time did change more than 3 time in last 30years. eg. Ukraine.
An option might be precompile ICU and bind the *.obj. But this needs to maintained, so it's no option for me(yet). Even if it would be a nice feature having ICU in pascal it's to much work for the Zeos maintaines, i guess.
Another option would be a property for the UTC offset. That would be ideal for me and would save my time. But it's not user intuitive i guess.? I'll check what we can do .. have some ideas.

What is miss is an option #3: raise a ticket on PG-Bugtracker. The behaiour is somewhat unexpeced! The more the doc's stating clearly:

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

Can you raise a ticket? Do you think it's worht it? I have some more inconsitencies according little/big-endians found using binary wire and Input/output(but i fixed all unexpected behaviore before i anabled the binary wire).

An what about input variables? Are parameter values handled same way or is there a beahvior difference?

[aehimself]

Completely offtopic. But when I read struggles like this I feel blessed that I store ALL dates in ALL my programs as GMT+0, no DST Unix Timestamp. No funky conversions, no reading / writing issues and handles multiple time zones like magic.

[MJFShark]

Exactly! We all know how much of a can of worms dealing with timezones is! I looked into making a Pg bug report on this, but I have a feeling that the answer would be "working as designed" or "won't fix since it will affect backwards compatibility."

-Mark

[MJFShark]

Michael,

Your fix is fantastic! Nicely done! I've verified that it works well in all the cases I've thrown at it so far. I have the need to update the offset anytime a "set timezone xxx" or "set time zone xxx" statement is run, so I put the UpdateTimestampOffset procedure into IZPostgreSQLConnection and changed ExecuteUpdatePrepared (included below) so that it would update the offset automatically. Basically my test script is:

set time zone 'America/New_York';
select current_timestamp;
set time zone 'UTC';
select current_timestamp;
set time zone 'America/Denver';
select current_timestamp;

Code: Select all

function TZAbstractPostgreSQLPreparedStatementV3.ExecuteUpdatePrepared: Integer;
var
  Status: TZPostgreSQLExecStatusType;
begin
  PrepareLastResultSetForReuse;
  Prepare;
  if BatchDMLArrayCount > 0
  then FRes := ExecuteDMLBatchWithUnnestVarlenaArrays
  else if (FRawPlanName = '') or Findeterminate_datatype
    then Fres := PGExecute
    else Fres := PGExecutePrepared;
  if Fres <> nil then begin
    Status := FPlainDriver.PQresultStatus(Fres);
    LastUpdateCount := RawToIntDef(FPlainDriver.PQcmdTuples(Fres), 0);
    if ((Status = PGRES_TUPLES_OK) or (Status = PGRES_SINGLE_TUPLE)) then begin
      if LastResultSet = nil then
        LastResultSet := CreateResultSet(fServerCursor);
      if (BindList.HasOutOrInOutOrResultParam) then
          FOutParamResultSet := CreateResultSet(fServerCursor);
    end else
      FPlainDriver.PQclear(Fres);
    // This is the added section (obviously the "startswith" calls would have to be changed to something more backwards compatible)
    if (Status = PGRES_COMMAND_OK) and (LastUpdateCount = 0) and
      (SQL.StartsWith('SET TIMEZONE ', True) or SQL.StartsWith('SET TIME ZONE ', True))  then
    begin
      FPostgreSQLConnection.UpdateTimestampOffset;
    end;
  end;
  Result := LastUpdateCount;
  { Logging Execution }
  if DriverManager.HasLoggingListener then
    DriverManager.LogMessage(cLoggingType[Findeterminate_datatype or (FRawPlanName = '')],Self);
end;

Many thanks!

-Mark

[MJFShark]

I'm not sure if this is useful, but just in case note that you can get the offset milliseconds directly using:

select extract(timezone from current_timestamp) * 1000000;

It looks like extract timezone was added in PostgreSQL 7.4.

-Mark

[EgonHugeist]

Hi Mark,

glad i could help even if the patch is not correct @all as you pointed out.
Missing things are:

• 1 Timestamp offset is changing whan a user sets the offsets manualy as you've done it.
  2 The offset also dependeds to sommer/winter time. Here in germany we've a UTC offset of +2 in sommer-time and an offset of +1 in Winter-time

So according your SQL Scan: It would fail for many reasons. Examples:
1.

Code: Select all

set time zone 'America/New_York';

versus

Code: Select all

SeT TIME zone 'America/New_York';

i wanna point you to the case sensitivity.
2.

Code: Select all

/*i wanna change my offset and i comment it in my query*/ set time zone 'America/New_York' 

here a leading comment would disturb your patch. And so on. Because of your example and the sommer/winter-time iusse i put the offset determination into that procedure as you noticed.

What need to be done:

• 1. It would be very helpful asking on PG mailing list or raise an ticket. If they wont't fix the behavior we can use the link as developer note reference in our code. So it's worth it doing that. Usually they implement a new protocol version if behavior changes.
  2. A thread-timer should be implemented to track the sommer/wintertime offset changes. Any ideas/proposals?
  3. According your "SET TIME ..." i've also ideas to handle the complex logic i wrote about -> we've a very powerfull tokenzer and methode

  Code: Select all

  function TZAbstractPostgreSQLPreparedStatementV3.GetRawEncodedSQL(const SQL: SQLString): RawByteString;

  seems the right place grap the "SET" methods.
  4. Finally: We need test is our testsuites!!!!

select extract(timezone from current_timestamp) * 1000000;

I'll check that! Nice catch! But (yet) it doesn't handle the "integer_datetimes"(mircosecond precision) vs "double_datetimes"(millisecond precision) postgres compile option.
Thank's for your quick answer, Mark. May is point you the the PG-Bugtracker again? Your english is almost perfect

[MJFShark]

Very nice! My particular instance never sends initial comments to the SQL property so I'm not affected by that (and I use the case insensitive .StartsWith call for the case part.) That's an interesting point about summer/winter time, we also have that (though there's talk of getting rid of it lol!) I'll have to think about the implications on that as I haven't had enough coffee yet.

Is the extract timezone call affected by integer_datetimes? Its just returning an integer. I may be missing a nuance on this, really it was just an aside since I was doing my own research on converting the timestamp and saw an example of it in a stack overflow post.

I'll look again into making a Pg bugreport. I was hesitant to use the bug reporting mailing list as I've had bad experiences using them in the past. If there's a ticket system I'll look into doing that. Thanks!

-Mark

[MJFShark]

Update! I've posted the bug to Pg's bug reporting system (which looks like it feeds the bug report mailing list.) Id #16923.

-Mark

[MJFShark]

And their response (no surprise here!)

Pg bug report response:

A quick look at timestamptz_send and timetz_send shows that they both just
send the internal representation; there isn't any intent to do any sort
of timezone adjustment. Even if there were some reason to argue that this
is wrong, the behavior is of sufficiently long standing that I doubt we
could change it.

So I think your solution is the best way to go.

-Mark

[EgonHugeist]

Yet i had no time to continue Mark. Thanks for the ticket approach. Anyway it should be acceptable to update the documentations according this "unexpected" behavior.
I keep an eye on that from Zeos side...