Skip to content

Instantly share code, notes, and snippets.

@NikolayS
Last active September 4, 2019 19:27
Show Gist options
  • Save NikolayS/ed5d0c1682bb7577661a34a27926b294 to your computer and use it in GitHub Desktop.
Save NikolayS/ed5d0c1682bb7577661a34a27926b294 to your computer and use it in GitHub Desktop.
SQL (Postgres) and timestamps 🤦‍
set timezone to 'EST';
with data(t) as (
select timestamp '2019-01-01 12:00'
)
select
t,
pg_typeof(t),
t at time zone 'UTC',
pg_typeof(t at time zone 'UTC')
from data;
t | pg_typeof | timezone | pg_typeof
---------------------+-----------------------------+------------------------+--------------------------
2019-01-01 12:00:00 | timestamp without time zone | 2019-01-01 07:00:00-05 | timestamp with time zone
(1 row)
with data(t) as (
select timestamptz '2019-01-01 12:00'
)
select
t,
pg_typeof(t),
t at time zone 'UTC',
pg_typeof(t at time zone 'UTC')
from data;
t | pg_typeof | timezone | pg_typeof
------------------------+--------------------------+---------------------+-----------------------------
2019-01-01 12:00:00-05 | timestamp with time zone | 2019-01-01 17:00:00 | timestamp without time zone
(1 row)
with data(t) as (
select time '12:00'
)
select
t,
pg_typeof(t),
t at time zone 'UTC',
pg_typeof(t at time zone 'UTC')
from data;
t | pg_typeof | timezone | pg_typeof
----------+------------------------+-------------+---------------------
12:00:00 | time without time zone | 17:00:00+00 | time with time zone
(1 row)
with data(t) as (
select timetz '12:00'
)
select
t,
pg_typeof(t),
t at time zone 'UTC',
pg_typeof(t at time zone 'UTC')
from data;
t | pg_typeof | timezone | pg_typeof
-------------+---------------------+-------------+---------------------
12:00:00-05 | time with time zone | 17:00:00+00 | time with time zone
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment