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

-- 设置当前会话的时区
set time zone 'Asia/Chongqing';

-- 显示当前会话的时区
show timezone;

-- 显示Postgres支持的时区
select *
from pg_timezone_names;

-- 显示当前时间
select now(), pg_typeof(now());

-- timestamptz是timestamp with time zone的别名；
-- timestamp是timestamp without time zone的别名。
select pg_typeof(timestamptz '2008-08-08'), pg_typeof(timestamp '2008-08-08');

-- 将字符串解析为带时区的时间，如果没有提供时区，会以当前会话时区为准
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';

-- 强制将timestamptz转换为timestamp，
-- 时区会被丢掉，日期和时间在文本上不变。
select timestamptz '2008-08-08 08:08:08+08' :: timestamp;

-- 强制将timestamp转换为timestamptz，
-- 时间以当前会话的时区进行解析。
select timestamp '2008-08-08 08:08:08' :: timestamptz;

-- 所以这些比较结果就在意料之中了
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';

-- 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');
-- 将字面时间加上时区
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');
-- 转换整列timestamp数据类型为timestamp with time zone，
-- 这里假设timestamp是按UTC时区来记录的。
alter table tbl
    alter ts_column type timestamptz
        using ts_column at time zone 'UTC';

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

-- date类型在和时间戳比较时，时分秒视为0
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';
-- date类型不带时区，时区按当前会话的时区进行解析
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 无法转换为 time with time zone
select timestamp '2008-08-08 00:00:59'::time with time zone;

-- time 时区不敏感，转换时按会话时区解析时间后丢掉时区
select timestamptz '2008-08-08 00:00:59+11'::time;

-- 直接提取timestamp的时间部分
select timestamp '2008-08-08 00:00:59'::time;

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

-- 北京奥运会开幕到2020元旦有多久了？
select timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08';

-- 有多少天呢？
select extract(days from
               timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08');

-- 2020年元旦的12年又12天又12小时之前是什么时候？
-- 这里没有关注时区
select timestamp '2020-01-01 00:00:00' - interval '12 years 12 days 12 hours';

-- 按会话时区生成2020年元旦到2021年元旦，每59天的序列
select generate_series(timestamptz '2020-01-01 00:00:00+08',
                       timestamptz '2021-01-01 00:00:00+08', interval '59 days');

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

-- 左闭右开区间
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)' :: tstzrange;

-- 全闭区间
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]' :: tstzrange;

-- 只限制一侧，另一侧为无限
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange;

-- 是否包含
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange @> '[2020-01-05 00:00:00+08, infinity)' :: tstzrange;

-- 是否相交
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;

-- 求交集
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;

-- 求差
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;

-- 启用btree_gist扩展
create extension btree_gist;

-- 创建预约表
create table room_reservation
(
    room   text,
    during tsrange,
    exclude using GIST (room with =, during with &&)
);

-- 预约101A的下午时段，OK
insert into room_reservation
values ('101A', '[2020-01-01 14:00, 2020-01-01 15:35)');

-- 预约203B的下午同一时段，OK
insert into room_reservation
values ('203B', '[2020-01-01 14:00, 2020-01-01 15:35)');

-- 尝试预约101A的下午时段，和之前的预约时间不完全重叠
insert into room_reservation
values ('101A', '[2020-01-01 11:00, 2020-01-01 15:00)');
-- 错误:  互相冲突的键值违反排他约束"room_reservation_room_during_excl"
-- 描述:  键(room, during)=(101A, ["2020-01-01 11:00:00","2020-01-01 15:00:00"))与已存在的键(room, during)=(101A, ["2020-01-01 14:00:00","2020-01-01 15:35:00"))冲突