I am using PostgreSQL 13.3 via Supabase.io. I have a table with a field called modified_at which has a type of timestamptz:
CREATE TABLE IF NOT EXISTS knowledge_views (
id uuid NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
modified_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);
I have an entry in it where the modified_at is: 2021-09-27T20:55:25.625Z (edit: it's not, Supabase currently hides the microseconds). None of the following statements work:
SELECT * FROM knowledge_views WHERE modified_at = timestamptz '2021-09-27T20:55:25.625Z';
SELECT * FROM knowledge_views WHERE modified_at = '2021-09-27T20:55:25.625Z'::timestamptz;
SELECT * FROM knowledge_views WHERE modified_at = to_timestamp(1632776125.625);
SELECT * FROM knowledge_views WHERE modified_at >= to_timestamp(1632776125.625) AND modified_at <= to_timestamp(1632776125.625);
However if take the last query and increment the milliseconds of the <= by one to 1632776125.626 then it correctly finds the row:
SELECT * FROM knowledge_views WHERE modified_at >= to_timestamp(1632776125.625) AND modified_at <= to_timestamp(1632776125.626);
Is there a way to select a row by an exact timestamptz value including milliseconds?
If that is not possible, is the (hacky) approach of adding 1 to the maximum millisecond value robust? Or should I also decrement one from the minimum value e.g.: >= to_timestamp(1632776125.624) AND modified_at <= to_timestamp(1632776125.626)?
** edit **
Using the following it shows the field is actually stored with microsecond precision:
SELECT modified_at as original, cast(extract(epoch from modified_at) * 1000000 as bigint) FROM knowledge_views;
| original | int8 |
|---|---|
| 2021-09-27T20:55:25.625Z | 1632776125625535 |
I realised it was working before as the client left the timestamp value string unchanged, i.e.:
supabase.from("knowledge_views").select("*").eq("modified_at", "2021-09-27T20:55:25.625535")
This broke when I used: new Date("2021-09-27T20:55:25.625535") which dropped the microseconds.
I have an entry in it where the modified_at is:
2021-09-27T20:55:25.625Z.
How do you know that's the exact value? I am asking because Postgres timestamps have microsecond resolution (6 fractional digits). Trailing zeroes are not displayed by default, but the chance that now() (in your odd column DEFAULT) produced a timestamp rounded to milliseconds exactly are 1 in 1000. Basics:
My guess is that your client reports timestamps rounded to milliseconds (maybe due to an unfortunate setting?). Run SELECT * FROM knowledge_views; in a sane client like the default PostgreSQL interactive terminal psql to get the actual value. Then you'll find that the = operator works as expected for timestamptz.
I would ditch the DEFAULT timezone('utc'::text, now()). timestamptz values are stored in UTC already, all you are doing is transposing the value in a way you don't want:
show timezone;
TimeZone
------------
US/Pacific
create table tstz_test(id integer, tstz_fld timestamptz);
insert into tstz_test values (1, now()), (2, timezone('UTC', now()));
select * from tstz_test ;
id | tstz_fld
----+-------------------------------
1 | 2021-09-27 16:56:35.964202-07
2 | 2021-09-27 23:56:35.964202-07
select tstz_fld AT TIME ZONE 'UTC' from tstz_test ;
timezone
----------------------------
2021-09-27 23:56:35.964202
2021-09-28 06:56:35.964202
select * from tstz_test where tstz_fld = '2021-09-27 23:56:35.964202Z'::timestamptz;
id | tstz_fld
----+-------------------------------
1 | 2021-09-27 16:56:35.964202-07
So milliseconds is not the issue.