Investigate: timestamp without timezone

[isoos]

It is not clear to me what should be the behavior when the server and the client have different timezones (both different than UTC). We should investigate and fix if needed.

[hendrik-brower]

I was just experimenting with ConnectionSettings, setting timezone to 'UTC' and 'America/New_York', 'EST'. Then running connection.execute with a series of statements:
select curent_timestamp;
set timezone to 'UTC';
select curent_timestamp;
set timezone to 'America/New_York';
select curent_timestamp;
set timezone to 'EST';
select curent_timestamp;
All select statements return the same string (with trailing Z) that reflects the database's timezone (ie alter database x set timezone y). This differs from the behavior I observe when running the running same queries through the psql command line utility. When running through command line utility, I get time stamps with +00, -04, -05 (reflecting the connection setting).

From this, it seems like the ConnectionSettings timezone value is not applied. It also seems like the set timezone statements do not affect the connection. Maybe each query is running in a separate session?

I think this would not be much of an issue if when retrieving a value, eg: res[0][0] as DateTime, returned a DateTime object that was a utc value. And when passing one, it converted it to utc. But it seems to return it as a local datetime, so you end up with a timeshift on the client size. Presently, to make things work as expected, I need to "insert (ct) values (@ct)" with ct=DateTime.now().toUtc(). Then after reading it, convert it to utc to get a matching result.

I haven't experimented with doing this in a session obtained via connection.run, though I would think a "connection" represents a session where as run(fn(session)) would represent a separate session. A bit more documentation on the top level classes to help outline these sorts of details would be great.

[hendrik-brower]

Just tried the same sequence with a session... same result. no returned values appear to respect the timezone set by "set timezone 'xx'" command. All values seem to be fixed to the database's setting.

[isoos]

@hendrik-brower: thank you for looking into this! Would you be also interested in writing a fix? I'd be happy to review and guide if needed.

[hendrik-brower]

I will have a bit of time in the second half of June. I'll try to take a look at it then.

…

On Sun, Jun 2, 2024 at 5:49 AM István Soós ***@***.***> wrote: @hendrik-brower <https://github.com/hendrik-brower>: thank you for looking into this! Would you be also interested in writing a fix? I'd be happy to review and guide if needed. — Reply to this email directly, view it on GitHub <#339 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABZATDY2GWL4QBJE2VYF7RLZFLTCRAVCNFSM6AAAAABH5IBBCGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNBTG43TOOBTGE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[insinfo]

@isoos @hendrik-brower
Today I was facing a problem precisely because of this driver problem of placing "Z" in all timestamp type columns, from what I read, timestamp type columns without timezone should not place "Z" because timestamp columns by definition do not have time zone information and cannot be considered UTC, I believe that the ideal solution is to modify the implementation to something like:

 case PostgreSQLDataType.timestampWithoutTimezone:
        try {
          final value = buffer.getInt64(0);
          //final date = DateTime.utc(2000).add(Duration(microseconds: value));      
          final date = DateTime(2000).add(Duration(microseconds: value));
          return date as T;
        } catch (e) {
          return null;
        }
      case PostgreSQLDataType.timestampWithTimezone:
        try {
          final value = buffer.getInt64(0);          
          final date = DateTime.utc(2000).add(Duration(microseconds: value));
          return date as T;
        } catch (e) {
          return null;
        }
      case PostgreSQLDataType.date:
        try {
          final value = buffer.getInt32(0);
          // final date = DateTime.utc(2000).add(Duration(days: value));          
          final date = DateTime(2000).add(Duration(days: value));
          return date as T;
        } catch (e) {
          return null;
        }

so timestamp columns without timezone do not have the "Z", which will avoid problems comparing dates that come from the database with DateTime.now();

from what I saw the C# npgsql driver does not put timestamp columns Without Timezone as UTC

I think this must be the correct behavior for postgresql-dart as well.
https://www.npgsql.org/doc/types/datetime.html
this same code implemented in dart has different behavior

// CREATE TABLE "sigep"."inscricoes" (
 // "id" int4 NOT NULL DEFAULT nextval('"sigep".inscricoes_id_seq'::regclass),
 // "titulo" text COLLATE "pg_catalog"."default" NOT NULL,
 // "anoExercicio" int4 NOT NULL,
//  "dataInicial" timestamp(6) NOT NULL,
//  "dataFinal" timestamp(6) NOT NULL,
//  "dataZ" timestamptz(6),
//  CONSTRAINT "id_pkey" PRIMARY KEY ("id")
// );

using Npgsql;
using System;
using System.Data;

var connString = "Host=localhost;Username=dart;Password=dart;Port=5435;Database=sistemas";

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connString);
var dataSource = dataSourceBuilder.Build();

var conn = await dataSource.OpenConnectionAsync();

//await using (var cnd1 = new NpgsqlCommand("INSERT INTO sigep.inscricoes (titulo,\"anoExercicio\",\"dataInicial\",\"dataFinal\",\"dataZ\") VALUES ('teste','2024','2024-07-10 17:10:00','2024-07-10 18:20:00','2024-07-10 15:35:23-03')", conn))
//{
   // cnd1.Parameters.AddWithValue("p", "Hello world");
    //await cnd1.ExecuteNonQueryAsync();
//}
await using (var cmd = new NpgsqlCommand("SELECT * FROM sigep.inscricoes WHERE  id=2", conn))

await using (var reader = await cmd.ExecuteReaderAsync())
{
    DateTime now = DateTime.Now;
    TimeZoneInfo localZone = TimeZoneInfo.Local;
    string standardName = localZone.StandardName;
    string daylightName = localZone.DaylightName;

    while (await reader.ReadAsync())
    {
        var dataInicial = reader.GetDateTime(3); 
        var dataFinal = reader.GetDateTime(4); 

        var dataZ = reader.GetDateTime(5);

        Console.WriteLine($"dataInicial {dataInicial.Kind == DateTimeKind.Utc} {dataInicial} {TimeZoneInfo.Local.GetUtcOffset(dataInicial)}");
        Console.WriteLine($"dataFinal {dataFinal.Kind == DateTimeKind.Utc} {dataFinal} {TimeZoneInfo.Local.GetUtcOffset(dataFinal)}");
        Console.WriteLine($"now {now.Kind == DateTimeKind.Utc} {now} {TimeZoneInfo.Local.GetUtcOffset(now)}");

        if(now >= dataInicial && now <= dataFinal)
        {
            Console.WriteLine("registration open");
        }
        else
        {
            Console.WriteLine("registration closed");
        }

        Console.WriteLine($"dataZ {dataZ.Kind == DateTimeKind.Utc} {dataZ} {TimeZoneInfo.Local.GetUtcOffset(dataZ)}");     

    }        
}

//dataInicial False 10/07/2024 17:10:00 -03:00:00
//dataFinal False 10/07/2024 18:20:00 -03:00:00
//now False 10/07/2024 16:51:14 -03:00:00
//registration closed
//dataZ True 10/07/2024 18:35:23 -03:00:00

I took a look at how I implemented this in the dargres driver and saw that it was like this

/// Decodes [value] into a [DateTime] instance.
  ///
  /// Note: it will convert it to local time (via [DateTime.toLocal])
  DateTime decodeDateTime(String value, int pgType) {
    // Built in Dart dates can either be local time or utc. Which means that the
    // the postgresql timezone parameter for the connection must be either set
    // to UTC, or the local time of the server on which the client is running.
    // This restriction could be relaxed by using a more advanced date library
    // capable of creating DateTimes for a non-local time zone.

    if (value == 'infinity' || value == '-infinity')
      throw _error('A timestamp value "$value", cannot be represented '
          'as a Dart object.');
    //if infinity values are required, rewrite the sql query to cast
    //the value to a string, i.e. your_column::text.

    var formattedValue = value;

    // Postgresql uses a BC suffix rather than a negative prefix as in ISO8601.
    if (value.endsWith(' BC'))
      formattedValue = '-' + value.substring(0, value.length - 3);

    if (pgType == TIMESTAMP) {
      formattedValue += 'Z';
    } else if (pgType == TIMESTAMPTZ) {
      // PG will return the timestamp in the connection's timezone. The resulting DateTime.parse will handle accordingly.
    } else if (pgType == DATE) {
      formattedValue = formattedValue + 'T00:00:00Z';
    }

    return DateTime.parse(formattedValue).toLocal();
  }

[isoos]

@insinfo: Thank you, that is great to see! I think this is a good starting material for a reduced test case + updated implementation. Would you be interested in preparing that too?

[insinfo]

I don't know if I'll have time to do this this week because I'm very busy, but from what I've seen I think the hardest part will be making the PostgresBinaryDecoder class have access to the timestamp information that PostgreSQL sends when authenticating so that it can use this information when decoding a timestamp with timezone, I think there will have to be changes in several places so that PostgresBinaryDecoder has access to this information.

https://github.com/isoos/postgresql-dart/blob/917fd326dcd1c0c09e4de1842d8b5d9bc1b8e7fe/lib/src/types/binary_codec.dart

I wonder if @simolus3 @busslina can help with this

[insinfo]

both the java implementation and the C# implementation result in "registration open" but in dart it results in "registration closed"

dart implementation

import 'package:intl/date_symbol_data_local.dart';
import 'package:intl/intl.dart';
import 'package:postgres/postgres.dart';

void main() async {
  await initializeDateFormatting('pt_BR');

  //Intl.systemLocale = await findSystemLocale();
  Intl.defaultLocale = 'pt_BR';

  print('Intl.defaultLocale  ${Intl.defaultLocale}');
  // Database connection
  final connection = await Connection.open(
      Endpoint(
          host: 'localhost',
          port: 5435,
          database: 'sistemas',
          username: 'dart',
          password: 'dart'),
      settings: ConnectionSettings(sslMode: SslMode.disable));

  // Current time and timezone info
  final now = DateTime.now();
  final dtInicio =
      now.subtract(Duration(minutes: 5)); 
  final dtFim = now.add(Duration(minutes: 120));
  final dt3 = DateTime(2024,07,11);
  print('now ${now.hour}h');
  final timezoneName = now.timeZoneName;
  print('Standard Timezone Name: $timezoneName');
  await connection.execute(
    Sql.indexed('''
INSERT INTO sigep.inscricoes (id, titulo, "anoExercicio", "dataInicial", "dataFinal", "dataZ", "data3")
  VALUES ('2', 'teste', '2024', '${dtInicio.toIso8601String()}', '${dtFim.toIso8601String()}',  '${now.toIso8601String()}',  '${dt3.toIso8601String()}')
ON CONFLICT (id)
DO UPDATE SET
   titulo = EXCLUDED.titulo,
  "anoExercicio" = EXCLUDED."anoExercicio",
  "dataInicial" = EXCLUDED."dataInicial",
  "dataFinal" = EXCLUDED."dataFinal",
  "dataZ" = EXCLUDED."dataZ",
  "data3" = EXCLUDED."data3"
WHERE
  sigep.inscricoes.id = '2' 
 '''),
  );

  final results = await connection.execute(
      Sql.indexed('SELECT * FROM sigep.inscricoes WHERE id = ? ',
          substitution: '?'),
      parameters: [2]);

  for (ResultRow item in results) {
    final row = item.toColumnMap();

    final dataInicial = row['dataInicial'] as DateTime;
    final dataFinal = row['dataFinal'] as DateTime;
    final dataZ = row['dataZ'] as DateTime;
    final data3 = row['data3'] as DateTime;

    print('dt3 == data3 ${dt3 == data3}');

    print(
        'dataInicial ${dataInicial.timeZoneOffset == Duration.zero ? 'UTC' : 'Local'} $dataInicial ${dataInicial.timeZoneOffset}');
    print(
        'dataFinal ${dataFinal.timeZoneOffset == Duration.zero ? 'UTC' : 'Local'} $dataFinal ${dataFinal.timeZoneOffset}');
    print(
        'now ${now.timeZoneOffset == Duration.zero ? 'UTC' : 'Local'} $now ${now.timeZoneOffset}');

    if (now.isAfter(dataInicial) && now.isBefore(dataFinal)) {
      print('registration open');
    } else {
      print('registration closed');
    }
    print(
      'dataZ ${dataZ.timeZoneOffset == Duration.zero ? 'UTC' : 'Local'} $dataZ ${dataZ.timeZoneOffset}',
    );
  }
  await connection.close();
}

// PS C:\MyDartProjects\new_sigep\backend> dart .\bin\teste_date.dart
// Intl.defaultLocale  pt_BR
// now 19h
// Standard Timezone Name: Hora oficial do Brasil
// dt3 == data3 false
// dataInicial UTC 2024-07-11 19:30:13.112467Z 0:00:00.000000
// dataFinal UTC 2024-07-11 21:35:13.112467Z 0:00:00.000000
// now Local 2024-07-11 19:35:13.112467 -3:00:00.000000
// registration closed
// dataZ UTC 2024-07-11 19:35:13.112467Z 0:00:00.000000

C# implementation

using Npgsql;
using System;
using System.Data;

var connString = "Host=localhost;Username=dart;Password=dart;Port=5435;Database=sistemas";

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connString);
var dataSource = dataSourceBuilder.Build();

var conn = await dataSource.OpenConnectionAsync();

// Current time and timezone info
var now = DateTime.Now;
var dtInicio = now.AddMinutes(-5);
var dtFim = now.AddMinutes(120);
var dt3 = new DateTime(2024, 7, 11);

Console.WriteLine($"now {now.Hour}h");
TimeZoneInfo localZone = TimeZoneInfo.Local;
var timezoneName = localZone.StandardName;
Console.WriteLine($"Standard Timezone Name: {timezoneName}");

var upsertCommandText = $@"
            INSERT INTO sigep.inscricoes (id, titulo, ""anoExercicio"", ""dataInicial"", ""dataFinal"", ""dataZ"", ""data3"")
            VALUES ('2', 'teste', '2024', '{dtInicio:yyyy-MM-ddTHH:mm:ss}', '{dtFim:yyyy-MM-ddTHH:mm:ss}', '{now:yyyy-MM-ddTHH:mm:ss}', '{dt3:yyyy-MM-ddTHH:mm:ss}')
            ON CONFLICT (id)
            DO UPDATE SET                
               titulo = EXCLUDED.titulo,
              ""anoExercicio"" = EXCLUDED.""anoExercicio"",
              ""dataInicial"" = EXCLUDED.""dataInicial"",
              ""dataFinal"" = EXCLUDED.""dataFinal"",
              ""dataZ"" = EXCLUDED.""dataZ"",
              data3 = EXCLUDED.data3
        ";

using (var upsertCommand = new NpgsqlCommand(upsertCommandText, conn))
{
    await upsertCommand.ExecuteNonQueryAsync();
}

await using (var cmd = new NpgsqlCommand("SELECT * FROM sigep.inscricoes WHERE  id=2", conn))


await using (var reader = await cmd.ExecuteReaderAsync())
{
        
   
    while (await reader.ReadAsync())
    {
        var dataInicial = reader.GetDateTime(reader.GetOrdinal("dataInicial"));
        var dataFinal = reader.GetDateTime(reader.GetOrdinal("dataFinal")); 
        var dataZ = reader.GetDateTime(reader.GetOrdinal("dataZ"));
        var data3 = reader.GetDateTime(reader.GetOrdinal("data3"));
        Console.WriteLine($"dt3 == data3 {dt3 == data3}");
        Console.WriteLine($"dataInicial {(dataInicial.Kind == DateTimeKind.Utc ? "UTC" : "Local")} {dataInicial} {TimeZoneInfo.Local.GetUtcOffset(dataInicial)}");
        Console.WriteLine($"dataFinal {(dataFinal.Kind == DateTimeKind.Utc ? "UTC" : "Local")} {dataFinal} {TimeZoneInfo.Local.GetUtcOffset(dataFinal)}");
        Console.WriteLine($"now {(now.Kind == DateTimeKind.Utc ? "UTC" : "Local")} {now} {TimeZoneInfo.Local.GetUtcOffset(now)}");

        if(now >= dataInicial && now <= dataFinal)
        {
            Console.WriteLine("registration open");
        }
        else
        {
            Console.WriteLine("registration closed");
        }

        Console.WriteLine($"dataZ {(now.Kind == DateTimeKind.Utc ? "UTC" : "Local")} {dataZ} {TimeZoneInfo.Local.GetUtcOffset(dataZ)}");     

    }        
}
// now 19h
// Standard Timezone Name: Hora oficial do Brasil
// dt3 == data3 True
// dataInicial Local 11/07/2024 19:32:14 -03:00:00
// dataFinal Local 11/07/2024 21:37:14 -03:00:00
// now Local 11/07/2024 19:37:14 -03:00:00
// registration open
// dataZ Local 11/07/2024 22:37:14 -03:00:00

java implementation

package org.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

public class Main {

    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5435/sistemas";
        String user = "dart";
        String password = "dart";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // Current time and timezone info
            LocalDateTime now = LocalDateTime.now();
            LocalDateTime dtInicio = now.minusMinutes(5);
            LocalDateTime dtFim = now.plusMinutes(120);
            LocalDate dt3 = LocalDate.of(2024, 7, 11);

            System.out.println("now " + now.getHour() + "h");
            ZoneId zoneId = ZoneId.systemDefault();
            String timezoneName = zoneId.getId();
            System.out.println("Standard Timezone Name: " + timezoneName);

            String upsertCommandText = String.format(
                    """
                    INSERT INTO sigep.inscricoes (id, titulo, "anoExercicio", "dataInicial", "dataFinal", "dataZ", "data3")
                    VALUES (2, 'teste', 2024, '%s', '%s', '%s', '%s')
                    ON CONFLICT (id)
                    DO UPDATE SET
                       titulo = EXCLUDED.titulo,
                       "anoExercicio" = EXCLUDED."anoExercicio",
                       "dataInicial" = EXCLUDED."dataInicial",
                       "dataFinal" = EXCLUDED."dataFinal",
                       "dataZ" = EXCLUDED."dataZ",
                       data3 = EXCLUDED.data3
                    """,
                    dtInicio.format(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss")),
                    dtFim.format(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss")),
                    now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss")),
                    dt3.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))
            );

            try (Statement upsertCommand = conn.createStatement()) {
                upsertCommand.executeUpdate(upsertCommandText);
            }

            String selectQuery = """
                SELECT * FROM sigep.inscricoes WHERE id=2
            """;

            try (PreparedStatement selectCommand = conn.prepareStatement(selectQuery);
                 ResultSet reader = selectCommand.executeQuery()) {

                while (reader.next()) {
                    LocalDateTime dataInicial = reader.getObject("dataInicial", LocalDateTime.class);
                    LocalDateTime dataFinal = reader.getObject("dataFinal", LocalDateTime.class);
                    OffsetDateTime dataZ = reader.getObject("dataZ", OffsetDateTime.class);
                    LocalDate data3 = reader.getObject("data3", LocalDate.class);

                    System.out.println("dt3 == data3 " + dt3.equals(data3));
                    System.out.println("dataInicial " + dataInicial + " " + zoneId.getRules().getOffset(dataInicial));
                    System.out.println("dataFinal " + dataFinal + " " + zoneId.getRules().getOffset(dataFinal));
                    System.out.println("now " + now + " " + zoneId.getRules().getOffset(now));

                    if (now.isAfter(dataInicial) && now.isBefore(dataFinal)) {
                        System.out.println("registration open");
                    } else {
                        System.out.println("registration closed");
                    }

                    System.out.println("dataZ " + dataZ + " " + dataZ.getOffset());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

19:28:06: Executing ':Main.main()'...

now 19h
Standard Timezone Name: America/Sao_Paulo
dt3 == data3 true
dataInicial 2024-07-11T19:23:06 -03:00
dataFinal 2024-07-11T21:28:06 -03:00
now 2024-07-11T19:28:06.601428600 -03:00
registration open
dataZ 2024-07-11T22:28:06Z Z
19:28:06: Execution finished ':Main.main()'.

[isoos]

Note: I've added timeZone parameter to withPostgresServer test grouping method (not much but hoping to spend more time on it next week).

[insinfo]

@isoos @hendrik-brower @busslina

I implemented TimeZone support in BinaryDecoder, with this PR the behavior of timestamp decoding with timezone follows the timezone set for the connection using the command "set timezone to 'America/Sao_Paulo'" returning a DateTime with the due timezone similar to the behavior exhibited by psql, and the decoding of timestamp without timezone becomes a local DateTime

#342

// ignore_for_file: depend_on_referenced_packages

import 'dart:convert';
import 'dart:io';
import 'package:intl/date_symbol_data_local.dart';
import 'package:intl/intl.dart';
import 'package:postgres/postgres.dart';

void main(List<String> args) async {
  
  final connection = await Connection.open(
    Endpoint(
      host: 'localhost',
      port: 5435,
      database: 'sistemas',
      username: 'dart',
      password: 'dart',
    ),
    settings: ConnectionSettings(sslMode: SslMode.disable),
  );
// print('now:  ${DateTime.now()} ${DateTime.now().timeZoneName}');

  var results = await connection.execute("select current_timestamp");
  var currentTimestamp = results.first.first as DateTime;
  print('dafault: $currentTimestamp ${currentTimestamp.timeZoneName}');
  print('local: ${currentTimestamp.toLocal()}');

  await connection.execute("set timezone to 'America/Sao_Paulo'");
  results = await connection.execute("select current_timestamp");
  currentTimestamp = results.first.first as DateTime;
  print(
      'America/Sao_Paulo: $currentTimestamp ${currentTimestamp.timeZoneName}');

  await connection.execute("set timezone to 'UTC'");
  results = await connection.execute("select current_timestamp");
  currentTimestamp = results.first.first as DateTime;
  print('UTC: $currentTimestamp ${currentTimestamp.timeZoneName}');

  await connection.execute("set timezone to 'America/New_York'");
  results = await connection.execute("select current_timestamp");
  currentTimestamp = results.first.first as DateTime;
  print('America/New_York: $currentTimestamp ${currentTimestamp.timeZoneName}');

  await connection.execute("set timezone to 'EST'");
  results = await connection.execute("select current_timestamp");
  currentTimestamp = results.first.first as DateTime;
  print('EST: $currentTimestamp ${currentTimestamp.timeZoneName}');

  results = await connection.execute(
      "SELECT 'infinity'::TIMESTAMP as col1, '-infinity'::TIMESTAMP as col2, 'infinity'::date as col3, '-infinity'::date as col3");
  print('main: $results');

  await connection.execute("set timezone to 'America/Sao_Paulo'");
  await connection.execute(
      '''UPDATE "sigep"."inscricoes" SET "dataZ" = \$1 WHERE "id" = 2 ''',
      parameters: [DateTime.now()]);
  results = await connection
      .execute("SELECT \"dataZ\" FROM sigep.inscricoes WHERE id=2");
  print('main: $results');

  await connection.close();
}

dafault: 2024-07-19 21:12:43.832166Z UTC
local: 2024-07-19 18:12:43.832166
America/Sao_Paulo: 2024-07-19 18:12:43.857805-0300 -03
UTC: 2024-07-19 21:12:43.944204Z UTC
America/New_York: 2024-07-19 17:12:43.950071-0400 EDT
EST: 2024-07-19 16:12:43.956152-0500 EST
main: [[null, null, null, null]]
main: [[2024-07-19 18:12:43.965045-0300]]

[leandroveronezi]

any update on the case?