Match PotsgreSQL timestamptz by exact value

Question

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.

What is your timezone set to? Are you running community PostgreSQL or some fork? How did you install it? What non-default settings do you have? What OS and version? — jjanes, Sep 27 '21 at 23:35

score 4 · Accepted Answer · answered Sep 28 '21 at 00:06

4

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:

Ignoring time zones altogether in Rails and PostgreSQL

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.

answered Sep 28 '21 at 00:06

Erwin Brandstetter

605,456
145
1,078
1,228

This may clarify the case in a client with 'unfortunate setting'. `select to_char(modified_at, '.FF3') mils, to_char(modified_at, '.US') micros, modified_at from knowledge_views;`. It clearly confirms the above - a truncation issue. – Stefanov.sm Sep 28 '21 at 06:58
`sane client` is the order of the day here! Thank you. – AJP Sep 28 '21 at 08:32
@Stefanov.sm that's very useful. Thank you. It showed: `| .625 | .625535 | 2021-09-27T20:55:25.625Z |` – AJP Sep 28 '21 at 09:04
I'm going to use `now()::timestamp(3)` on insert, update so that it's trivial to use the datetimes strings with javascript Date which lacks microseconds. – AJP Sep 28 '21 at 09:27

score 1 · Answer 2 · answered Sep 28 '21 at 00:02

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.

Match PotsgreSQL timestamptz by exact value

2 Answers2

Linked