PostgreSQL TIMESTAMP类型 时间戳操作
PostgreSQL提供两种存储时间戳的数据类型:不带时区的TIMESTAMP和带时区的TIMESTAMPTZ。
TIMESTAMP数据类型可以同时存储日期和时间,但它不存储时区。这意味着,当修改了数据库服务器所在的时区时,它里面存储的值不会改变。
TIMESTAMPTZ数据类型在存储日期和时间的同时还能正确处理时区。PostgreSQL使用UTC值来存储TIMESTAMPTZ数据。在向TIMESTAMPTZ字段插入值的时候,PostgreSQL会自动将值转换成UTC值,并保存到表里。当从一个TIMESTAMPTZ字段查询数据的时候,PostgreSQL会把存储在其中的UTC值转换成数据库服务器、用户或当前连接所在的时区。
TIMESTAMP和TIMESTAMPTZ都使用8字节存储空间。如下所示:
SELECT typname, typlen FROM pg_type WHERE typname~'^timestamp'; typname|typlen -------------+-------- timestamp|8 timestamptz|8 (2rows)
重要提示,TIMESTAMPTZ并不会存储时区,它只是了UTC值,然后会和当前时区进行转换。
时间戳示例
首先,创建一个含有TIMESTAMP和TIMESTAMPTZ的表:
CREATETABLEtimestamp_demo(tsTIMESTAMP,tstzTIMESTAMPTZ);
接下来,将数据库服务器的时区设置为America/Los_Angeles:
SETtimezone='America/Los_Angeles';
顺便说一句,可以使用SHOWTIMEZONE语句来显示当前时区:
SHOWTIMEZONE;
然后,插入一些示例数据:
INSERTINTOtimestamp_demo(ts,tstz) VALUES ( '2016-06-2219:10:25-07', '2016-06-2219:10:25-07' );
最后,查询:
SELECT ts FROM timestampz_demo; ts|tstz ---------------------+------------------------ 2016-06-2219:10:25|2016-06-2219:10:25-07 (1row)
再把时区设置为America/New_York:
SETtimezone='America/New_York';
再查询一次,结果如下:
ts|tstz ---------------------+------------------------ 2016-06-2219:10:25|2016-06-2222:10:25-04 (1row)
TIMESTAMP类型字段的值不变,而TIMESTAMPTZ类型字段的值变成了当前时区下的时间。
补充:postgresqltimestamptimestamptz使用注意事项
postgresql日期时间类型分为timestamp[(p)][withouttimezone]和timestamp[(p)]withtimezone,它们的区别在于一个无时区,另一有时区,存储大小均为8字节.
同时也要注意time[(p)][withouttimezone]和time[(p)]withtimezone类型,无时区存储大小为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*frompg_timezone_names
或查询东亚/中国时区定义
select*frompg_timezone_nameswhereutc_offset='+08:00:00';
查询结果中的"PRC","Asia/Shanghai","Asia/Chongqing"均表示中国
生成一个日期时间示例
selectmake_timestamptz(1970,1,1,0,0,0.0,'Asia/Shanghai')
注意避免使用timestamp类型相关函数,如:make_timestamp
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。