PostgreSQL的时区字段区别

PostgreSQL的时区字段区别
PostgreSQL的时间字段类型有time,timestamp,date和interval等,其中time和timestamp还有时区的区别,即with/without time zone的区别。

这些类型在官方的定义是:
Name Storage Size Description Low Value High Value Resolution timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time zone) 4713 BC 294276 AD 1 microsecond / 14 digits timestamp [ (p) ] with time zone 8 bytes both date and time, with time zone 4713 BC 294276 AD 1 microsecond / 14 digits date 4 bytes date (no time of day) 4713 BC 5874897 AD 1 day time [ (p) ] [ without time zone ] 8 bytes time of day (no date) 00:00:00 24:00:00 1 microsecond / 14 digits time [ (p) ] with time zone 12 bytes times of day only, with time zone 00:00:00+1459 24:00:00-1459 1 microsecond / 14 digits interval [ fields ] [ (p) ] 12 bytes time interval -178000000 years 178000000 years 1 microsecond / 14 digits

with和without time zone两者有什么区别,名字上看一个是带时区的,另一个是不带时区的,时区的基准是格林威治时间。这对于数据的存储上来说,区别就是时间数据的末尾带不带时区标志,即+/-时区,比如中国(prc),时区是东八区,带时区标志的话就是+08。

示例:
1.查看测试机服务端时区
[postgres@localhost ~]$ more /database/pgdata/postgresql.conf |grep timezonelog_timezone = 'US/Pacific'timezone = 'US/Pacific'#timezone_abbreviations = 'Default'     # Select the set of available time zone                                        # share/timezonesets/.
2.客户端查看
postgres=# show timezone;  TimeZone  ------------ US/Pacific(1 row)postgres=# select now()::timestamp with time zone,now()::timestamp without time zone;              now              |            now             -------------------------------+---------------------------- 2013-07-04 02:32:59.073604-07 | 2013-07-04 02:32:59.073604(1 row)--更改客户端时区并查看时间postgres=# set timezone to 'prc';SETpostgres=# show timezone; TimeZone ---------- PRC(1 row)postgres=# select now()::timestamp with time zone,now()::timestamp without time zone;              now              |            now             -------------------------------+---------------------------- 2013-07-04 17:33:18.872205+08 | 2013-07-04 17:33:18.872205(1 row)--美国西7区与中国东八区相差15个小时,在例子中也能体现(17-2=15h)
3.客户端时区的更改说明
在服务端有一些时区的配置信息,安装的时候就给装上了,客户端时区的更改并不能随意,如不能设置不存在的时区,比如我可以设置PRC,asia/shanghai,但不能设置PPP,asia/beijing等,这些配置信息是放在$PGHOME/share/timezone里面,模板可以参考shared/timezonesets/。

4.其他 如果取当前时间数据时,没有特别标注不要时区,那select出来的结果一般是带时区的,比如select now()即等同于select now()::timestamp with time zone 。客户端取当前时间默认是取服务端,个人习惯,通常建表字段时都是without time zone居多

推荐阅读