SQL-时间-UTC-时间戳-日期-年查询在PG+PostGIS

时间-UTC-时间戳-日期查询
select timestamp '2004-10-19 10:23:54';
查询系统当前完整时间
select now();
select current_timestamp;
查询系统当前日期
select current_date;
查询系统当前时间
select current_time;
时间计算-2年后
select now() + interval '2 years';
select now() + interval '2 year';
select now() + interval '2 y';
select now() + interval '2 Y';
select now() + interval '2Y';
时间计算-1月后
select now() + interval '1 month';
时间计算-3周前
select now() - interval '3 week';
时间计算-10分钟后
select now() + '10 min';
计算两个时间差
select age(now(), timestamp '1989-02-05');
select age(timestamp '2007-09-15');
时间字段的截取 --- EXTRACT函数EXTRACT(field FROM source)，其中 field 表示取的时间对象，source 表示取的日期来源，类型为 timestamp、time 或 interval。
取年份
select extract(year from now());
取月份
select extract(month from now());
取日
select extract(day from timestamp '2013-04-13');
select extract(DAY FROM interval '40 days 1 minute');
查看今天是这一年中的第几天
select extract(doy from now());
查看现在距1970-01-01 00:00:00 UTC 的秒数
select extract(epoch from now());
epoch 值转换回时间戳(epoch值也就是utc值，转换为时间戳)
select timestamp with time zone 'epoch' + 1369755555 * interval '1 second';
时间戳转成epoch值
select extract(epoch from now());
具体时间戳转换为utc(即epoch)
select extract(epoch from timestamp without time zone '1970-01-01 01:00:00');
select extract(epoch from to_timestamp('2019-03-26 14:37:26','yyyy-MM-DD hh24:mi:ss'));   ----->  1553582246
epoch 值转换回时间戳(epoch值也就是utc值，转换为时间戳)
select timestamp with time zone 'epoch' + 1553582246 * interval '1 second';  ----->  2019-03-26 14:37:26
字符串到时间戳
select to_timestamp('2019-03-26 14:37:26','yyyy-MM-DD hh24:mi:ss');
根据具体时间进行计算，一个小时的utc
select extract(epoch from interval '+1 hours');
select extract(epoch from interval '-1 hours');
字符串到时间戳和日期
select to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss');
select to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss');
对日期进行比较，输出boolean
select current_timestamp <= to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss')
select current_timestamp <= to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') flag;
时间戳根据具体格式转换为字符串
SELECT to_char((TIMESTAMP WITH TIME ZONE'epoch' + 1447898857 * INTERVAL '1 second' ),'yyyy-MM');
utc转timestamp
epoch(utc) ---> timestamp
select timestamp with time zone 'epoch' + 1553582246 * interval '1 second';
select timestamp with time zone 'epoch' + 1553582256 * interval '1 second';
select timestamp with time zone 'epoch' + 1553582266 * interval '1 second';
select timestamp with time zone 'epoch' + 1553582276 * interval '1 second';
2019-03-26 14:37:26
2019-03-26 14:37:36
2019-03-26 14:37:46
2019-03-26 14:37:56
timestamp ---> epoch(utc)
select extract(epoch from to_timestamp('2019-03-26 14:37:26','yyyy-MM-DD hh24:mi:ss'));
select extract(epoch from to_timestamp('2019-03-26 14:38:26','yyyy-MM-DD hh24:mi:ss'));
select extract(epoch from to_timestamp('2019-03-26 14:39:26','yyyy-MM-DD hh24:mi:ss'));
select extract(epoch from to_timestamp('2019-03-26 14:40:26','yyyy-MM-DD hh24:mi:ss'));
1553582246
1553582306
1553582366
1553582426
select extract(epoch from to_timestamp('2019-03-26 14:37:26.57','yyyy-MM-DD hh24:mi:ss'));
1553582246