About DateTime parsing

[busslina]

When reading a Date field on the database, will the given DateTime object care about the original time zone/offset?

[busslina]

I just saw this, so I suppose that yes, it cares.

I was thinking that maybe you use the timezone package to do that, but using unix time seems to solve that too.

[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]]

#339