Hyston blog
About • Archive • RSS

Pgsql timezones

November 3, 2023

It’s been 0️⃣ days since last time I was confused about timezones. Recently I noticed that we store local timezone in our db as timestamp with time zone: 2023-11-03T14:20:00+01:00 instead of UTC time.
When I used switch “Npgsql.EnableLegacyTimestampBehaviour” it seemed, that it saved UTC value into database, but on reading DateTime kind was Local, which was incorrect.
Long story short: I didn’t notice big red warning in Npgsql documentation. When we save UTC to db, it uses local timezone to represent values.
For example, if I change local timezone: SET TIMEZONE ‘Asia/Brunei’ it would show time as 2023-11-03T21:20:00+08:00, but data wouldn’t have changed, only how it displayed on the screen. And it transfers into C# layer properly.
More than that, RFC3339 allows it and states that it is a correct format to display UTC.

← Prev entry