Skip to content

Instantly share code, notes, and snippets.

@jc00ke
Last active January 2, 2016 16:09
Show Gist options
  • Save jc00ke/8328039 to your computer and use it in GitHub Desktop.
Save jc00ke/8328039 to your computer and use it in GitHub Desktop.
psql timezones
-- $> PGTZ=PST8PDT psql
-- If the PGTZ environment variable is set in the frontend environment
-- of a client based on libpq, libpq will automatically SET TIMEZONE to
-- the value of PGTZ during connection start-up.
select
created_at,
created_at at time zone 'UTC' at_tz_utc,
created_at at time zone 'PST' at_tz_pst,
created_at at time zone 'PST' - interval '8 hours' at_tz_pst_minus_8_hours
from
widgets
;
-- created_at | at_tz_utc | at_tz_pst | at_tz_pst_minus_8_hours
------------------------------+-------------------------------+-------------------------------+-------------------------------
-- 2013-12-31 16:34:26.710356 | 2013-12-31 08:34:26.710356-08 | 2013-12-31 16:34:26.710356-08 | 2013-12-31 08:34:26.710356-08
-- $> psql
select
created_at,
created_at at time zone 'UTC' at_tz_utc,
created_at at time zone 'PST' at_tz_pst,
created_at at time zone 'PST' - interval '8 hours' at_tz_pst_minus_8_hours
from
widgets
;
-- created_at | at_tz_utc | at_tz_pst | at_tz_pst_minus_8_hours
------------------------------+-------------------------------+-------------------------------+-------------------------------
-- 2013-12-31 16:34:26.710356 | 2013-12-31 16:34:26.710356+00 | 2014-01-01 00:34:26.710356+00 | 2013-12-31 16:34:26.710356+00
Table "public.widgets"
Column | Type | Modifiers | Storage |
--------------------+-----------------------------+------------------------------------------------------------+----------+
id | integer | not null default nextval('contributions_id_seq'::regclass) | plain |
created_at | timestamp without time zone | not null | plain |
@selenamarie
Copy link

The column 'created_at' is currently 'timestamp without time zone' You need to use timestamp *with* time zone to get this all correct.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment