postgresql timestamp timestamptz 使用注意事项

原创,转载请注明出处!

postgresql日期时间类型分为timestamp [ (p) ] [ without time zone ]和timestamp [ (p) ] with time zone,它们的区别在于一个无时区,另一有时区,存储大小均为8字节.

同时也要注意time [ (p) ] [ without time zone ]和time [ (p) ] with time zone类型,无时区存储大小为8字节,有时区存储大小为12字节.

postgresql中日期时间类型缩写:

timestamp

timestamptz

date (仅年月日部份,存储大小为4字节)

time

timetz

interval(表示间隔,存储大小为16字节)

interval可以通过函数make_interval来创建,支持年\月\星期\天\小时\分钟\秒的间隔.如now() + make_interval(days => 10)表示10天后,now() - make_interval(secs => 30)表示30秒之前

为保证数据的准确性在保存\使用\计算过程中应尽量使用timestamptz和timetz,尽量避免使用timestamp和time

UTC+8(EAT - 东亚标准时间/中国标准时间(BJT))

注意整个中国使用相同的时区，这就使得这个时区特别的大。在中国最西部的地区，太阳最高的时候是下午3点，在最东部是上午11点。单纯从地理规划来看，整个中国横跨了从东五区（UTC+5）到东九区（UTC+9）共计五个时区。

在postgresql中查询时区的定义

select * from pg_timezone_names

或查询东亚/中国时区定义

select * from pg_timezone_names where utc_offset = '+08:00:00';

查询结果中的"PRC","Asia/Shanghai","Asia/Chongqing"均表示中国

生成一个日期时间示例

select  make_timestamptz(1970,1,1,0,0,0.0,'Asia/Shanghai') 

注意避免使用timestamp类型相关函数,如:make_timestamp

[日期/时间类型]https://www.postgresql.org/docs/current/static/datatype-datetime.html
[日期/时间函数和操作符]https://www.postgresql.org/docs/current/static/functions-datetime.html

图片来源,[维基]https://zh.wikipedia.org/wiki/%E6%97%B6%E5%8C%BA%E5%88%97%E8%A1%A8