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

Maybe: `new.latest_report := current_timestamp at time zone 'PST';`. Note that using `=` for assignment in PL/pgSQL is deprecated (actually even undocumented). You should use `:=` http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT — , Aug 21 '14 at 21:42
@a_horse_with_no_name Got error: "syntax error at or near 'PST'" — cbunn, Aug 21 '14 at 22:06
Aside: never quote the language name `plpgsql`. It's an identifier. — Erwin Brandstetter, Aug 21 '14 at 23:20
Works for me: http://sqlfiddle.com/#!15/30ab7/1 You probably didn't adjust the code to deal with the single quotes embedded in single quotes. You should get used to using "dollar quoting" for function bodies. — , Aug 22 '14 at 06:01
@a_horse_with_no_name I ran it again with the correct dollar quoting and I still get the time 7 hours ahead of PST. — cbunn, Aug 22 '14 at 18:58

score 9 · Answer 1 · edited May 23 '17 at 12:31

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:

Ignoring timezones altogether in Rails and PostgreSQL

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

I ran the SET timezone='America/Anchorage' and got a syntax error at or near the '='. — cbunn, Aug 22 '14 at 19:02
Maybe I cannot set the timezone of the session in CartoDB, although it runs off of PostgreSQL. — cbunn, Aug 22 '14 at 22:57
@cbunn: You did not mention CartoDB. I don't know it, but there should be a way to set the time zone *somehow*. — Erwin Brandstetter, Aug 22 '14 at 23:13

Set timestamp column's timezone in PostgreSQL

1 Answers1

Linked