Menu
In Postgres I want to store table's last update/insert time. Microsoft SQL Server offers a type timestamp which is automatically maintained by the database.
But timestamp in Postgres works differently, it is not updated automatically and the column is always null.
236
You can do it via checking last modification time of table's file. In postgresql,every table correspond one or more os files,like this: select relfilenode from pg_class where relname = 'test'; the relfilenode is the file name of table "test".
The PostgreSQL function LOCALTIMESTAMP returns the current date and time (of the machine running that instance of PostgreSQL) as a timestamp value. It uses the 'YYYY-MM-DD hh:mm:ss. nnnnnnn' format, where: YYYY is a 4-digit year.
To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
PostgreSQL supports the full set of SQL date and time types, shown in Table 8.9.
In postgresql, you have to use a trigger. You can follow this link on how to do it https://x-team.com/blog/automatic-timestamps-with-postgresql/ .
To summarize the article you can do the following:
Create the Pl/Pgsql function that will be triggered:
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Create your table
CREATE TABLE mytable (
id SERIAL NOT NULL PRIMARY KEY,
content TEXT,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
And finally add the trigger:
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON mytable
FOR EACH ROW
EXECUTE FUNCTION trigger_set_timestamp();
You can find more informations about the question here: https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table
Hope it'll help you.
147
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
