3

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();
Improve this question
5
  • 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 := postgresql.org/docs/current/static/… Commented Aug 21, 2014 at 21:42
  • @a_horse_with_no_name Got error: "syntax error at or near 'PST'" Commented Aug 21, 2014 at 22:06
  • Aside: never quote the language name plpgsql. It's an identifier. Commented Aug 21, 2014 at 23:20
  • Works for me: 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. Commented Aug 22, 2014 at 6: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. Commented Aug 22, 2014 at 18:58

1 Answer 1

Reset to default
10

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.