-- timestamp/timestamptz --
---------------------------------------

-- Set timezone of current session
set time zone 'Asia/Chongqing';

-- Show timezone of current session
show timezone;

-- Show timezone that Postgres supports
select *
from pg_timezone_names;

-- Current time is in timestamptz
select now(), pg_typeof(now());

-- timestamptz is alias to timestamp with time zone;
-- timestamp is alias to timestamp without time zone.
select pg_typeof(timestamptz '2008-08-08'), pg_typeof(timestamp '2008-08-08');

-- Parse date and time string to timestamptz,
-- session timezone is used if no timezone provided
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';

-- Convert timestamptz to timestamp:
-- the timezone is gone, the date and time keeps unchanged literally.
select timestamptz '2008-08-08 08:08:08+08' :: timestamp;

-- Covert timestamp to timestamptz:
-- session timezone is used.
select timestamp '2008-08-08 08:08:08' :: timestamptz;

-- The same rules apply to comparision
select timestamp '2008-08-08 08:08:08' = timestamptz '2008-08-08 08:08:08+08';
select timestamp '2008-08-08 08:08:08' = timestamptz '2008-08-08 08:08:08+06';

-- What the time is in Roma at 2008-08-08 08:08:08+08?
select timestamptz '2008-08-08 08:08:08+08' at time zone 'Europe/Rome',
       pg_typeof(now() at time zone 'Europe/Rome');
-- Add timezone to timestamp to get timestamptz
select timestamp '2008-08-08 08:08:08' at time zone 'Asia/Chongqing',
       pg_typeof(timestamp '2008-08-08 08:08:08' at time zone 'Asia/Chongqing');
select timestamp '2008-08-08 08:08:08' at time zone 'Europe/Vienna',
       pg_typeof(timestamp '2008-08-08 08:08:08' at time zone 'Europe/Vienna');
-- Alter column data type from timestamp to timestamptz,
-- assuming timestamp is recorded in UTC.
alter table tbl
    alter ts_column type timestamptz
        using ts_column at time zone 'UTC';

-- date --
---------------------------------------

-- date has time of 00:00:00 during comparision
-- between timestamp/timestamptz
select timestamp '2008-08-08 00:00:00' = date '2008-08-08';
select timestamp '2008-08-08 00:00:00.000001' = date '2008-08-08';
-- current session timezone is used
-- during comparision between timestamptz
select timestamptz '2008-08-08 00:00:00+08' = date '2008-08-08';
select timestamptz '2008-08-08 00:00:00+12' = date '2008-08-08';

-- time / time with time zone --
---------------------------------------

-- time with time zone
select timestamptz '2008-08-08 00:00:59+11'::time with time zone;

-- timestamp can not be converted into time with time zone
select timestamp '2008-08-08 00:00:59'::time with time zone;

-- time is not timezone-aware,
-- so it takes the time part of timestamptz at current session timezone.
select timestamptz '2008-08-08 00:00:59+11'::time;

-- time takes time part of timestamp, directly.
select timestamp '2008-08-08 00:00:59'::time;

-- interval --
---------------------------------------

-- How long does it take from Beijing Olympics to 2020 new year's day?
select timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08';

-- How many days then?
select extract(days from
               timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08');

-- What the time is, 12 years 12 day and 12 hours before 2020 new year's day?
-- Note timezone is not considered here.
select timestamp '2020-01-01 00:00:00' - interval '12 years 12 days 12 hours';

-- Generate series between 2020 new year's day and 2021 every 59 days,
-- at current session timezone.
select generate_series(timestamptz '2020-01-01 00:00:00+08',
                       timestamptz '2021-01-01 00:00:00+08', interval '59 days');

-- range --
---------------------------------------

-- Left closed right open range
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)' :: tstzrange;

-- Closed range
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]' :: tstzrange;

-- Range with one side unbound
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange;

-- Included?
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange @> '[2020-01-05 00:00:00+08, infinity)' :: tstzrange;

-- Intersected?
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)' :: tstzrange
           && '[2020-01-01 08:00:00+08, 2020-01-01 17:15:00+08]' :: tstzrange;

-- Intersection
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)' :: tstzrange
           * '[2020-01-01 08:00:00+08, 2020-01-01 17:15:00+08]' :: tstzrange;

-- Difference
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]' :: tstzrange
           - '(2020-01-01 08:00:00+08, 2020-01-01 17:15:00+08]' :: tstzrange;

-- Following example needs btree_gist extension
create extension btree_gist;

-- A table for room reservation
create table room_reservation
(
    room   text,
    during tsrange,
    exclude using GIST (room with =, during with &&)
);

-- Reserve 101A for afternoon, OK
insert into room_reservation
values ('101A', '[2020-01-01 14:00, 2020-01-01 15:35)');

-- Reserve 203B for the same period, OK
insert into room_reservation
values ('203B', '[2020-01-01 14:00, 2020-01-01 15:35)');

-- Try to reserve 101A, with a period not entirely overlapped
insert into room_reservation
values ('101A', '[2020-01-01 11:00, 2020-01-01 15:00)');
-- Postgres is going to complain about constraint violation.