0

To prevent older version overwrite new version, in this simple function:

create function myupdate(paramts timestamp without time zone, ...)
  language plpgsql AS
$$
begin
-- step 1 compare current record timestamp vs. supplied timestamp
if exists (select 1 from record where ts <> paramts and ...) then
    raise exception 'A newer version exists, cannot update.';
end if;
...
end
$$;

ts definition is the same timestamp without time zone.

paramts value is supplied by a function:

create function myfetch(...)
  language plpgsql AS
$$
begin
    return query select ts, ... from record where ...;
end
$$;

What node API and Angular client UI gets is 2021-04-16T21:37:35.878Z so is the value submitted to myupdate(). However, at one of our West Coast server, during execution inside of myupdate(), ts is auto-converted to PST 2021-04-16 14:37:35.878694 and has 3 extra digits at right.

How to compare both in UTC and same precision?

Jeb50
  • 6,272
  • 6
  • 49
  • 87

2 Answers2

1

You should use timestamptz (timestamp with time zone) instead of timestamp (timestamp without time zone) to avoid any time zone confusion. In the table, in the function, in the whole food chain. Values are always stored as UTC time internally then, and comparisons work correctly automatically (comparing points in time independent of time zones).

See:

Either way, both types have microsecond resolution, i.e. 6 factional decimal digits. Your first example got truncated somehow, possibly by your client in the display.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks Erwin for the suggestion, even though the Answer doesn't answer all my question, I still accept it because `tz` is the best solution. I'll post my complete Answer and Solution below. – Jeb50 Apr 18 '21 at 18:54
0

Here is the complete Answer:

  1. Use tz = Timestamp with time zone for column definition.
  2. tz always feeds clients with UTC values, for example I'm using node/pg-promise, frontend is Angular/TS, they all get value like 2021-04-17T22:42:57.610Z.
  3. UTC out, UTC submitted back (assuming you don't touch timestamp), is it good enough? Yes and No. Yes if your client supports 6-digits decimal second otherwise No for JavaScript and variants as it supports only 3-digits.
  4. Solution is to round to 2-digits then compare

if (select tableTimestamp::timestamp(2) = parameter::timestamp(2) from mytable where ...) then Update mytable ...; else raise exception ...; end if;

P.S., with and without time zone is confusing.

Jeb50
  • 6,272
  • 6
  • 49
  • 87