with和without time zone两者有什么区别
1.区别
1)名字上看一个是带时区的,另一个是不带时区的,查出来的时间是一样的,只是一个带时区标志,一个不带而已,时区的基准是格林威治时间UTC。
2)这对于数据的显示上来说,区别就是时间数据的末尾带不带时区标志,即+/-时区,比如中国(prc),时区是东八区,带时区标志的话就是+08。
数据库内部还是都存储的是UTC格式时间,根据当前的时区进行转换显示。
2.查看配置文件中时区的配置:
[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/.
3.客户端时区的更改说明
在服务端有一些时区的配置信息,安装的时候就给装上了,客户端时区的更改并不能随意设置。
这些配置信息是放在$PGHOME/share/timezone里面。
4.实验:
美国西7区与中国东八区相差15个小时,格林威治时间UTC和东八区相差8个小时
test=# set timezone="US/Pacific"; SET test=# show timezone; TimeZone ------------ US/Pacific (1 row) test=# select now()::timestamp with time zone, now()::timestamp without time zone; now | now -------------------------------+---------------------------- 2019-08-20 11:41:47.578319-07 | 2019-08-20 11:41:47.578319 (1 row) test=# set timezone=prc; SET test=# show timezone; TimeZone ---------- PRC (1 row) test=# select now()::timestamp with time zone, now()::timestamp without time zone; now | now -------------------------------+---------------------------- 2019-08-21 02:42:01.898303+08 | 2019-08-21 02:42:01.898303 (1 row) test=# set timezone=UTC; SET test=# show timezone; TimeZone ---------- UTC (1 row) test=# select now()::timestamp with time zone, now()::timestamp without time zone; now | now -------------------------------+---------------------------- 2019-08-20 18:42:09.018571+00 | 2019-08-20 18:42:09.018571 (1 row)
北京、上海都是PRC东八区,时间是一样的
test=# set timezone="Asia/Shanghai"; SET test=# show timezone; TimeZone --------------- Asia/Shanghai (1 row) test=# select now()::timestamp with time zone, now()::timestamp without time zone; now | now -------------------------------+---------------------------- 2019-08-21 02:51:26.704907+08 | 2019-08-21 02:51:26.704907 (1 row) test=# set timezone="Asia/Beijing"; SET test=# select now()::timestamp with time zone, now()::timestamp without time zone; now | now -------------------------------+---------------------------- 2019-08-21 02:51:41.534930+08 | 2019-08-21 02:51:41.534930 (1 row) test=# set timezone=PRC; SET test=# select now()::timestamp with time zone, now()::timestamp without time zone; now | now -------------------------------+---------------------------- 2019-08-21 02:51:54.704583+08 | 2019-08-21 02:51:54.704583 (1 row)