SQL (Postgres) and timestamps 🤦‍

at_time_zone.sql

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)