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 timezone

log_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';

SET

postgres=# 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居多