Match PotsgreSQL timestamptz by exact value-CodePudding

I am using PostgreSQL 13.3. 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. 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)?

CodePudding user response：

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.

CodePudding user response：

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.