create extension timestampandtz; set time zone 'US/Eastern'; select '9-18-2014 8:15pm'::timestampandtz; timestampandtz --------------------------------------- Thu Sep 18 20:15:00 2014 @ US/Eastern (1 row) set time zone 'US/Pacific'; select '9-18-2014 8:15pm'::timestampandtz; timestampandtz --------------------------------------- Thu Sep 18 20:15:00 2014 @ US/Pacific (1 row) select '9-18-2014 8:15pm @ US/Eastern'::timestampandtz; timestampandtz --------------------------------------- Thu Sep 18 20:15:00 2014 @ US/Eastern (1 row) select '9-18-2014 8:15pm @ US/Pacific'::timestampandtz; timestampandtz --------------------------------------- Thu Sep 18 20:15:00 2014 @ US/Pacific (1 row) select '9-18-2014 8:15pm @ foobar'::timestampandtz; ERROR: missing timezone ID "foobar" while parsing timestampandtz "9-18-2014 8:15pm" LINE 1: select '9-18-2014 8:15pm @ foobar'::timestampandtz; ^ select '9-18-2014 8:15pm @ US/Eastern'::timestampandtz::timestamp; timestamp -------------------------- Thu Sep 18 20:15:00 2014 (1 row) select '9-18-2014 8:15pm @ US/Pacific'::timestampandtz::timestamp; timestamp -------------------------- Thu Sep 18 20:15:00 2014 (1 row) set time zone 'US/Eastern'; select '9-18-2014 8:15pm @ US/Eastern'::timestampandtz::timestamptz; timestamptz ------------------------------ Thu Sep 18 20:15:00 2014 EDT (1 row) select '9-18-2014 8:15pm @ US/Pacific'::timestampandtz::timestamptz; timestamptz ------------------------------ Thu Sep 18 23:15:00 2014 EDT (1 row) select '9-18-2014 8:15pm @ US/Eastern'::timestampandtz + interval '3 hours'; ?column? --------------------------------------- Thu Sep 18 23:15:00 2014 @ US/Eastern (1 row) select '9-18-2014 8:15pm @ US/Pacific'::timestampandtz + interval '3 hours'; ?column? --------------------------------------- Thu Sep 18 23:15:00 2014 @ US/Pacific (1 row) select '9-18-2014 8:15pm @ US/Eastern'::timestampandtz + interval '3 days'; ?column? --------------------------------------- Sun Sep 21 20:15:00 2014 @ US/Eastern (1 row) select '9-18-2014 8:15pm @ US/Pacific'::timestampandtz + interval '3 days'; ?column? --------------------------------------- Sun Sep 21 20:15:00 2014 @ US/Pacific (1 row) select '9-18-2014 8:15pm @ US/Eastern'::timestampandtz + interval '3 months'; ?column? --------------------------------------- Thu Dec 18 20:15:00 2014 @ US/Eastern (1 row) select '9-18-2014 8:15pm @ US/Pacific'::timestampandtz + interval '3 months'; ?column? --------------------------------------- Thu Dec 18 20:15:00 2014 @ US/Pacific (1 row) select ('9-18-2014 8:15pm @ US/Eastern'::timestampandtz + interval '3 hours') at time zone 'utc'; timezone -------------------------- Fri Sep 19 03:15:00 2014 (1 row) select ('9-18-2014 8:15pm @ US/Pacific'::timestampandtz + interval '3 hours') at time zone 'utc'; timezone -------------------------- Fri Sep 19 06:15:00 2014 (1 row) select ('9-18-2014 8:15pm @ US/Eastern'::timestampandtz + interval '3 days') at time zone 'utc'; timezone -------------------------- Mon Sep 22 00:15:00 2014 (1 row) select ('9-18-2014 8:15pm @ US/Pacific'::timestampandtz + interval '3 days') at time zone 'utc'; timezone -------------------------- Mon Sep 22 03:15:00 2014 (1 row) select ('9-18-2014 8:15pm @ US/Eastern'::timestampandtz + interval '3 months') at time zone 'utc'; timezone -------------------------- Fri Dec 19 01:15:00 2014 (1 row) select ('9-18-2014 8:15pm @ US/Pacific'::timestampandtz + interval '3 months') at time zone 'utc'; timezone -------------------------- Fri Dec 19 04:15:00 2014 (1 row) select '12-18-2014 8:15pm @ US/Eastern'::timestampandtz - interval '3 hours'; ?column? --------------------------------------- Thu Dec 18 17:15:00 2014 @ US/Eastern (1 row) select '12-18-2014 8:15pm @ US/Pacific'::timestampandtz - interval '3 hours'; ?column? --------------------------------------- Thu Dec 18 17:15:00 2014 @ US/Pacific (1 row) select '12-18-2014 8:15pm @ US/Eastern'::timestampandtz - interval '3 months'; ?column? --------------------------------------- Thu Sep 18 20:15:00 2014 @ US/Eastern (1 row) select '12-18-2014 8:15pm @ US/Pacific'::timestampandtz - interval '3 months'; ?column? --------------------------------------- Thu Sep 18 20:15:00 2014 @ US/Pacific (1 row) select ('12-18-2014 8:15pm @ US/Eastern'::timestampandtz - interval '3 hours') at time zone 'utc'; timezone -------------------------- Thu Dec 18 22:15:00 2014 (1 row) select ('12-18-2014 8:15pm @ US/Pacific'::timestampandtz - interval '3 hours') at time zone 'utc'; timezone -------------------------- Fri Dec 19 01:15:00 2014 (1 row) select ('12-18-2014 8:15pm @ US/Eastern'::timestampandtz - interval '3 months') at time zone 'utc'; timezone -------------------------- Fri Sep 19 00:15:00 2014 (1 row) select ('12-18-2014 8:15pm @ US/Pacific'::timestampandtz - interval '3 months') at time zone 'utc'; timezone -------------------------- Fri Sep 19 03:15:00 2014 (1 row) create table times (dt timestampandtz); create index ix_times_dt on times (dt); insert into times values ('9-18-2014 8:15pm @ US/Eastern'); insert into times values ('9-18-2014 5:16pm @ US/Pacific'); insert into times values ('9-18-2014 8:17pm @ US/Eastern'); insert into times values ('9-18-2014 7:18pm @ US/Central'); insert into times values('9-18-2014 20:19'::timestamptz); insert into times values('9-18-2014 20:20'::timestamp); select dt,dt::timestamp,dt::timestamptz from times order by times.dt asc; dt | dt | dt ---------------------------------------+--------------------------+------------------------------ Thu Sep 18 20:15:00 2014 @ US/Eastern | Thu Sep 18 20:15:00 2014 | Thu Sep 18 20:15:00 2014 EDT Thu Sep 18 17:16:00 2014 @ US/Pacific | Thu Sep 18 17:16:00 2014 | Thu Sep 18 20:16:00 2014 EDT Thu Sep 18 20:17:00 2014 @ US/Eastern | Thu Sep 18 20:17:00 2014 | Thu Sep 18 20:17:00 2014 EDT Thu Sep 18 19:18:00 2014 @ US/Central | Thu Sep 18 19:18:00 2014 | Thu Sep 18 20:18:00 2014 EDT Thu Sep 18 20:19:00 2014 @ US/Eastern | Thu Sep 18 20:19:00 2014 | Thu Sep 18 20:19:00 2014 EDT Thu Sep 18 20:20:00 2014 @ US/Eastern | Thu Sep 18 20:20:00 2014 | Thu Sep 18 20:20:00 2014 EDT (6 rows) select dt,dt at time zone 'US/Eastern' from times order by times.dt asc; dt | timezone ---------------------------------------+-------------------------- Thu Sep 18 20:15:00 2014 @ US/Eastern | Thu Sep 18 20:15:00 2014 Thu Sep 18 17:16:00 2014 @ US/Pacific | Thu Sep 18 20:16:00 2014 Thu Sep 18 20:17:00 2014 @ US/Eastern | Thu Sep 18 20:17:00 2014 Thu Sep 18 19:18:00 2014 @ US/Central | Thu Sep 18 20:18:00 2014 Thu Sep 18 20:19:00 2014 @ US/Eastern | Thu Sep 18 20:19:00 2014 Thu Sep 18 20:20:00 2014 @ US/Eastern | Thu Sep 18 20:20:00 2014 (6 rows) select dt,dt at time zone 'US/Central' from times order by times.dt asc; dt | timezone ---------------------------------------+-------------------------- Thu Sep 18 20:15:00 2014 @ US/Eastern | Thu Sep 18 19:15:00 2014 Thu Sep 18 17:16:00 2014 @ US/Pacific | Thu Sep 18 19:16:00 2014 Thu Sep 18 20:17:00 2014 @ US/Eastern | Thu Sep 18 19:17:00 2014 Thu Sep 18 19:18:00 2014 @ US/Central | Thu Sep 18 19:18:00 2014 Thu Sep 18 20:19:00 2014 @ US/Eastern | Thu Sep 18 19:19:00 2014 Thu Sep 18 20:20:00 2014 @ US/Eastern | Thu Sep 18 19:20:00 2014 (6 rows) select tzmove('9-18-2014 8:15pm @ US/Eastern'::timestampandtz, 'US/Eastern'); tzmove --------------------------------------- Thu Sep 18 20:15:00 2014 @ US/Eastern (1 row) select tzmove('9-18-2014 8:15pm @ US/Eastern'::timestampandtz, 'US/Pacific'); tzmove --------------------------------------- Thu Sep 18 17:15:00 2014 @ US/Pacific (1 row) select tzmove('9-18-2014 8:15pm @ US/Eastern'::timestampandtz, 'foobar'); ERROR: missing timezone ID "foobar" select to_char('09-18-2014 8:15pm'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam'); to_char ------------------------ 18 Sep 2014 08:15:00pm (1 row) select to_char('09-18-2014 8:15pm'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam TZ'); to_char ---------------------------- 18 Sep 2014 08:15:00pm EDT (1 row) select to_char('09-18-2014 8:15pm @ US/Central'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam'); to_char ------------------------ 18 Sep 2014 08:15:00pm (1 row) select to_char('09-18-2014 8:15pm @ US/Central'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam TZ'); to_char ---------------------------- 18 Sep 2014 08:15:00pm CDT (1 row) select to_char('12-18-2014 8:15pm'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam'); to_char ------------------------ 18 Dec 2014 08:15:00pm (1 row) select to_char('12-18-2014 8:15pm'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam TZ'); to_char ---------------------------- 18 Dec 2014 08:15:00pm EST (1 row) select to_char('12-18-2014 8:15pm @ US/Central'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam'); to_char ------------------------ 18 Dec 2014 08:15:00pm (1 row) select to_char('12-18-2014 8:15pm @ US/Central'::timestampandtz, 'DD Mon YYYY HH12:MI:SSam TZ'); to_char ---------------------------- 18 Dec 2014 08:15:00pm CST (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(-1); ERROR: TIMESTAMP(-1) AND TIME ZONE precision must not be negative LINE 1: select '09-18-2014 8:15:19.12345678'::timestampandtz(-1); ^ select '09-18-2014 8:15:19.12345678'::timestampandtz(7); WARNING: TIMESTAMP(7) AND TIME ZONE precision reduced to maximum allowed, 6 LINE 1: select '09-18-2014 8:15:19.12345678'::timestampandtz(7); ^ timestampandtz ---------------------------------------------- Thu Sep 18 08:15:19.123457 2014 @ US/Eastern (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(6); timestampandtz ---------------------------------------------- Thu Sep 18 08:15:19.123457 2014 @ US/Eastern (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(5); timestampandtz --------------------------------------------- Thu Sep 18 08:15:19.12346 2014 @ US/Eastern (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(4); timestampandtz -------------------------------------------- Thu Sep 18 08:15:19.1235 2014 @ US/Eastern (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(3); timestampandtz ------------------------------------------- Thu Sep 18 08:15:19.123 2014 @ US/Eastern (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(2); timestampandtz ------------------------------------------ Thu Sep 18 08:15:19.12 2014 @ US/Eastern (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(1); timestampandtz ----------------------------------------- Thu Sep 18 08:15:19.1 2014 @ US/Eastern (1 row) select '09-18-2014 8:15:19.12345678'::timestampandtz(0); timestampandtz --------------------------------------- Thu Sep 18 08:15:19 2014 @ US/Eastern (1 row) select '8/1/2014 8:15 @ US/Pacific'::timestampandtz - '8/1/2014 8:15 @ US/Eastern'::timestampandtz; ?column? ----------- @ 3 hours (1 row) select '8/1/2014 8:15 @ US/Pacific'::timestampandtz - '8/1/2014 11:15 @ US/Eastern'::timestampandtz; ?column? ---------- @ 0 (1 row) select '12/1/2014 8:15 @ US/Eastern'::timestampandtz - '10/1/2014 8:15 @ US/Eastern'::timestampandtz; ?column? ------------------ @ 61 days 1 hour (1 row) select date_trunc('hour', '8/15/2014 8:15 @ US/Eastern'::timestampandtz); date_trunc --------------------------------------- Fri Aug 15 08:00:00 2014 @ US/Eastern (1 row) select date_trunc('hour', '8/15/2014 8:15 @ US/Pacific'::timestampandtz); date_trunc --------------------------------------- Fri Aug 15 08:00:00 2014 @ US/Pacific (1 row) select date_trunc('day', '8/15/2014 8:15 @ US/Eastern'::timestampandtz); date_trunc --------------------------------------- Fri Aug 15 00:00:00 2014 @ US/Eastern (1 row) select date_trunc('day', '8/15/2014 8:15 @ US/Pacific'::timestampandtz); date_trunc --------------------------------------- Fri Aug 15 00:00:00 2014 @ US/Pacific (1 row) select date_trunc('month', '8/15/2014 8:15 @ US/Eastern'::timestampandtz); date_trunc --------------------------------------- Fri Aug 01 00:00:00 2014 @ US/Eastern (1 row) select date_trunc('month', '8/15/2014 8:15 @ US/Pacific'::timestampandtz); date_trunc --------------------------------------- Fri Aug 01 00:00:00 2014 @ US/Pacific (1 row) select date_trunc_at('day', '2014-09-15 20:15:00 @ US/Pacific', 'US/Eastern'); date_trunc_at --------------------------------------- Sun Sep 14 21:00:00 2014 @ US/Pacific (1 row) select date_trunc_at('day', '2014-09-15 23:15:00 @ US/Pacific', 'US/Eastern'); date_trunc_at --------------------------------------- Mon Sep 15 21:00:00 2014 @ US/Pacific (1 row) select date_part('hour', '9/1/2015 8:15am @ US/Eastern'::timestampandtz); date_part ----------- 8 (1 row) select date_part('hour', '9/1/2015 8:15am @ US/Pacific'::timestampandtz); date_part ----------- 8 (1 row) select '8/15/2014'::timestampandtz='8/15/2014'::date; ?column? ---------- t (1 row) select '8/15/2014 @ US/Eastern'::timestampandtz='8/15/2014'::date; ?column? ---------- t (1 row) select '8/15/2014 @ US/Pacific'::timestampandtz='8/15/2014'::date; ?column? ---------- t (1 row) select '8/15/2014 9:15pm'::timestampandtz::date='8/15/2014'::date; ?column? ---------- t (1 row) select '8/15/2014 9:15pm @ US/Eastern'::timestampandtz::date='8/15/2014'::date; ?column? ---------- t (1 row) select '8/15/2014 9:15pm @ US/Pacific'::timestampandtz::date='8/15/2014'::date; ?column? ---------- t (1 row) select to_char('8/15/2014 9:15pm @ US/Eastern'::timestampandtz, 'HH24:MI YYYY-MM-DD'); to_char ------------------ 21:15 2014-08-15 (1 row) select to_char('8/15/2014 9:15pm @ US/Pacific'::timestampandtz, 'HH24:MI YYYY-MM-DD'); to_char ------------------ 21:15 2014-08-15 (1 row)