CODEWITHSUNDEEP
postgresqltimezonetimestamp
cbunn
cbunn

Reputation: 205

Set timestamp column's timezone in PostgreSQL

I have a trigger on a PostgreSQL table that updates a timestamp field, but I want to get it in the right timezone. How can I set my column to always be in 'PST' by default? Here is my trigger: 

ALTER TABLE coastal ADD latest_report TIMESTAMP;

ALTER TABLE coastal ALTER COLUMN latest_report 
SET DEFAULT CURRENT_TIMESTAMP;

UPDATE coastal SET latest_report=CURRENT_TIMESTAMP;

CREATE OR REPLACE FUNCTION coastal_latest_report()
  RETURNS TRIGGER AS '
BEGIN
   NEW.latest_report = NOW();
   RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER coastal_latest_report_modtime BEFORE UPDATE
  ON coastal FOR EACH ROW EXECUTE PROCEDURE
  coastal_latest_report();

Upvotes: 3

Views: 9748

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658897

How can I set my column to always be in 'PST' by default?

This is not possible / a misunderstanding. A timestamp column is not in any time zone. The time zone is never saved, not even for timestamp with time zone (timestamptz), which always saves UTC time. That name is a tad bit misleading, I'll give you that.

You have two very simple options:

  • Save now() to a timestamptz column.
  • Save now() to a timestamp column. (The time zone offset is truncated in the cast.)

If you want timestamps to be displayed for the 'PST' time zone, set the time zone setting of your session to that time zone (if it is not set already). For instance:

SET timezone = 'America/Anchorage'

Ample details in this related answer:

To find your time zone name:

If you want to save the original time zone of input values:

Upvotes: 10

Related Questions