I was surprised select date_trunc('day','2021-02-16 20:00+04'::timestamptz); returned 2021-02-17 00:00:00+08 in postgres. It's like we moved forward in time from 16th to 17th. I want to check my understanding and find sources explaining this (ideally official). The session has set timezone…
From docs https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS TIMESTAMP '2004-10-19 10:23:54+02' is a timestamp with time zone Question: Why select pg_typeof(TIMESTAMP '2004-10-19 10:23:54+02'); gives timestamp without time zone? Expecting timestamp with time zone as docs say. Postgres version SELECT version(); shows PostgreSQL 14.14 (Debian 14.14-1.pgdg120+1)…
set timezone = 'Asia/Ho_Chi_Minh'; I set times displayed to GMT+7. select '2021-02-16 09:00' AT TIME ZONE 'Asia/Singapore'; select '2021-02-16 09:00+06' AT TIME ZONE 'Asia/Singapore'; Output 2021-02-16 10:00:00 2021-02-16 11:00:00 Because AT TIME ZONE converts timestamp without time zone to timestamp…
I'm using sqlx 0.7.3 and time 0.3.34. I have this struct: #[derive(Debug, sqlx::FromRow)] pub struct Game { pub id: String, pub created_at: time::OffsetDateTime, pub date_time: time::OffsetDateTime, pub score: i64, // and many other fields } I can read with queries…
I have a postgres database, that stores some accounting data. The accounting data have a timestamp (with timezone). I would like to aggregate/group the accounting data by date. But the date should be in the user's/requested timezone not UTC. SELECT…
I am saving a C# DateTime.MinValue to a PostgreSQL column of type timestamp with timezone with EFCore 7.05 and Ngpsql 7.04 Date = {1/01/0001 12:00:00 AM} Kind = Utc When I load the column, the value becomes Date = {1/01/0001…
My table has a "Timestamp" column (text type) with YYYY-MM-DDTHH:MM:SSZ formatted dates. I want to generate a timestamptz formatted column with a continuous UTC timestamp but have been unable to do it. I have tried many methods suggested in forums…