Post
by JimKnicely » Tue Apr 11, 2017 7:33 pm
There are 4 time data types in play:
TIME - which keeps hours/minutes/seconds kind of thing
TIMETZ - which keeps what time PLUS a time zone. TIMETZ is an awful data type and I'd avoid it like the plague.
TIMESTAMP - which keeps microseconds from a fixed time, hence it can handle hours/minutes/seconds/days/months/years
TIMESTAMPTZ - which is like that but converts between GMT and your local time zone automatically
So let's deconstruct the time example.
-- If we give a timezone, and ask for the same, it is preserved
CBear=> SELECT TIME '10:23:54 CDT' AT TIME ZONE 'CDT';
timezone
-------------
10:23:54-05
(1 row)
-- Asking for time in my time zone to be converted to CDT
CBear=> SELECT TIME '10:23:54 EDT' AT TIME ZONE 'CDT';
timezone
-------------
09:23:54-05
(1 row)
-- Time constant is assumed to be in my EDT time zone, then converted to CDT.
CBear=> SELECT TIME '10:23:54' AT TIME ZONE 'CDT';
timezone
-------------
09:23:54-05
(1 row)
The timestamp example is working differently. This is because AT TIME ZONE does 2 different things, one to timestamps, and one to timestamptz!
First is to take a TIMESTAMPTZ, and turn it into a TIMESTAMP at the time zone you ask. That could be what you wanted.
CBear=> SELECT TIMESTAMPTZ '2004-10-19 10:23:54 EDT' AT TIME ZONE 'CDT';
timezone
---------------------
2004-10-19 09:23:54
(1 row)
-- Assumes the constant came from your timezone
CBear=> SELECT TIMESTAMPTZ '2004-10-19 10:23:54' AT TIME ZONE 'CDT';
timezone
---------------------
2004-10-19 09:23:54
(1 row)
-- Use of timezone in the string
CBear=> SELECT TIMESTAMPTZ '2004-10-19 10:23:54 CDT' AT TIME ZONE 'CDT';
timezone
---------------------
2004-10-19 10:23:54
The other thing it does is take a TIMESTAMP and turn it into a TIMESTAMPTZ by saying, in essence, that it was recorded at the time zone you say. For display purposes, it will get displayed in the local timezone.
-- Comes back looking the same
CBear=> SELECT TIMESTAMP '2004-10-19 10:23:54' AT TIME ZONE 'EDT';
timezone
------------------------
2004-10-19 10:23:54-04
(1 row)
-- The timestamp is treated as if originally CDT, so it displays an hour higher in EDT (-4)
CBear=> SELECT TIMESTAMP '2004-10-19 10:23:54' AT TIME ZONE 'CDT';
timezone
------------------------
2004-10-19 11:23:54-04
(1 row)
Jim Knicely
Note: I work for
Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.