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.
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.
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