Question 272448 in PostgreSQL Question posted in
The official documentation can be found here.

.Net PostgreSQL NpgsqlBatch. Apply SET TIME ZONE to select query

How to apply SET TIME ZONE to NpgsqlCommand?

Attempt 1. SET TIME ZONE in CommandText, separated from select with ;

using var cmd = connection.CreateCommand();
cmd.CommandText = "SET TIME ZONE '+02:00'; select "t0"."start_time" from "task"."tasks" "t0" where "t0"."number" = 217";
await connection.OpenAsync();
using var reader = await cmd.ExecuteReaderAsync();

Attempt 2. Use NpgsqlBatch

await connection.OpenAsync();

using var batch = new NpgsqlBatch(connection);
batch.BatchCommands.Add(new NpgsqlBatchCommand("SET TIME ZONE '+02:00'"));
batch.BatchCommands.Add(new NpgsqlBatchCommand("select "t0"."start_time" from "task"."tasks" "t0" where "t0"."number" = 217"));
await batch.PrepareAsync();

using var reader = await batch.ExecuteReaderAsync();

start_time column is type of timestamptz

In neither attempt did SET TIME ZONE work. As I understand it: 1 attempt did not work because npgsql split command text by semicolon ; into batches. So SET TIME ZONE is in individual batch and does not apply to select.

More about batches in npgsql
https://www.infoq.com/news/2022/06/Database-Command-Batching/

When I run query in PgAdmin, SET TIME ZONE works – I got different result

SET TIME ZONE '+02:00';
select 
    "t0"."start_time"
from "task"."tasks" "t0"
where "t0"."number" = 217
Tags:

2

Answers


  1. 0

    SET TIME ZONE has no effect with good reason.NpgSQL returns timestamp with time zone as a DateTime value whose Kind is DateTimeKind.UTC. This is explained in the type mapping docs. PostgreSQL itself stores timestamp with timezone as a UTC value, without the original offset or timezone name. From the PostgreSQL docs:

    For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

    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

    The value returned by NpgSQL is accurate – a UTC DateTime. This shouldn’t be a problem if the application treats this as a UTC time. Unfortunately, functions like ToString ignore the Kind property. In this case a good choice would be to convert the value to a local date in C# with DateTime.ToLocalTime, eg :

    while(reader.Read())
{
   ...
   var date=reader.GetDateTime(3).ToLocalTime();
   ...
}

    Another option is to convert the value to a DateTimeOffset, ensuring UTC and local are never confused:

    var date=new DateTimeOffset(reader.GetDateTime(3));

    Another option is to use AT TIME ZONE to have PostgreSQL convert the time to a specific timezone. Don’t use the offset though, as this will change based on DST rules. It’s far safer to use the IANA timezone name, eg:

    var sql="""
select "start_time" AT TIME ZONE 'Europe/Berlin' 
from "task"."tasks" "t0"
where "t0"."number" = 217
""";

cmd.CommandText=sql;
    Login or Signup to reply.
  2. 0

    Your code above does set the time zone successfully; if you execute SHOW TIME ZONE afterwards you should get back the value that you set.

    However, Npgsql is – very much by design – not sensitive to whatever the time zone is set to. Technically, in PostgreSQL TIME ZONE only has an effect when a timestamp with time zone is converted to a textual representation, to a timestamp without time zone, etc. However, Npgsql does not convert the timestamp to text; it retrieves it from PostgreSQL in its binary encoding, which is simply the UTC timestamp (which is also what is stored on disk).

    Therefore, changing TIME ZONE simply does not affect what you get from Npgsql.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search