Set timestamp column's timezone in PostgreSQL

Question

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();

Answer 1

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:

• Are PostgreSQL column names case-sensitive?

To find your time zone name:

• Set custom timezone in Django/PostgreSQL (Indian Standard Time)

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

• Preserve timezone in PostgreSQL timestamptz type