Postgresql datetime resolution of milliseconds compatible with Javascript Date

Question

I have a timestamp column in a PostgreSQL (13.3) table in Supabase. It currently stores timestamps to microsecond resolution. I want to send the timestamps to a javascript client and use them to query back (via Supabase) for exactly matching rows by this timestamp value. Javascript Date objects drop the microseconds and only store the milliseconds. I could store the microseconds string along with the Date object (that I need for presentation in various UI components) in the javascript client but this seems unnecessarily messy and error prone as the duplicated data could get out of sync.

Is it possible to force the timestamp values created in PostgreSQL to always be to millisecond and not microsecond resolution? Currently I am using the following for the CREATE TABLE and inside the upsert function:

CREATE TABLE abc (
  -- other fields
  modified_at timestamp without time zone DEFAULT now()::timestamp(3) NOT NULL
);

  UPDATE abc
  SET
    -- other fields
    modified_at = now()::timestamp(3)
  WHERE modified_at = item.modified_at;

Related to this question.

score 2 · Answer 1 · answered Sep 29 '21 at 10:41

Timestamp accepts an argument for the number of decimal places to keep so you can define your table using:

modified_at timestamp(3) without time zone DEFAULT now() NOT NULL

This means it will always have 000 microseconds which will always work with Javascript's Date object.

score 0 · Answer 2 · answered Sep 29 '21 at 16:04

If you want you can use CREATE DOMAIN:

create domain js_ts timestamptz(3);
create table js_tz_test(id int, ts_fld js_ts);

\d js_tz_test
Table "public.js_tz_test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 ts_fld | js_ts   |        

insert into js_tz_test values (1, now());

select * from js_tz_test ;
 id |           ts_fld           
----+----------------------------
  1 | 2021-09-29 09:00:45.626-07

select now()::js_ts;
            now             
----------------------------
 2021-09-29 09:04:55.388-07

Postgresql datetime resolution of milliseconds compatible with Javascript Date

2 Answers2