Set the time zone of a timestamp column in PostgreSQL

Question

I have a trigger in a PostgreSQL table that updates the timestamp field, but I want to get it in the right time zone. 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 impossible / misunderstanding. The timestamp column is not in the time zone. The time zone is never saved, even for timestamp with time zone ( timestamptz ), which always saves UTC time. This name is a little mistaken, I will give it to you.

You have two very simple options:

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

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

 SET timezone='America/Anchorage' 

Details in this answer:

• Ignoring Time Zones in General in Rails and PostgreSQL

To find your time zone name:

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

If you want to keep the original time zone for input values:

• Save timezone in type timestamptz PostgreSQL