Search code examples
postgresqltimestamputccurrent-time

PostgreSQL select now()::timestamp differs from default now()::timestamp

In my program every table has a column last_modified: 

last_modified int8 DEFAULT (date_part('epoch'::text, now()::timestamp) * (1000)::double precision) NOT NULL

For update I added a trigger:

CREATE OR REPLACE FUNCTION sync_lastmodified() RETURNS trigger AS $$
BEGIN
  NEW.last_modified := (date_part('epoch'::text, now()::timestamp) * (1000)::double precision);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER
  sync_lastmodified
BEFORE UPDATE ON
  ourtable
FOR EACH ROW EXECUTE PROCEDURE
  sync_lastmodified();

They should write current time as a long value into the last_modified column on update/insert. However, it is not working as I expected.

To reproduce the issue, I did update and got the following:

last_modified value equals 1543576224455 (Friday November 30, 2018 16:10:24 (pm) in time zone Asia/Tashkent (+05))

Almost at the same time I run the function now from pgAdmin:

SELECT now()

and got the result:

2018-11-30 11:10:36.891426+05

To check system time within a few seconds I run timedatectl status from terminal and got the following result:

enter image description here

The question is why the function now() gives a time with 5 hours difference when I run it from trigger or as a default value when insert?

Solution

  • epoch will give you the number of seconds since the epoch. As the documentation says:

    epoch

    For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative)

    Since you are offset by 5 hours from UTC, that explains the difference.