Search code examples
c#.netpostgresqlnpgsql

.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
Solution

  • 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.