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
(2 rows)

重要提示，timestamptz 并不会存储时区，它只是了 utc 值，然后会和当前时区进行转换。

时间戳示例

首先，创建一个含有 timestamp 和 timestamptz 的表：

create table timestamp_demo (ts timestamp, tstz timestamptz);

接下来，将数据库服务器的时区设置为 america/los_angeles：

set timezone = 'america/los_angeles';

顺便说一句，可以使用 show timezone 语句来显示当前时区：

show timezone;

然后，插入一些示例数据：

insert into timestamp_demo (ts, tstz)
values
 (
 '2016-06-22 19:10:25-07',
 '2016-06-22 19:10:25-07'
 );

最后，查询：

select
 ts
from
 timestampz_demo;
     ts     |     tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)

再把时区设置为 america/new_york：

set timezone = 'america/new_york';

再查询一次，结果如下：

     ts     |     tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)

timestamp 类型字段的值不变，而 timestamptz 类型字段的值变成了当前时区下的时间。

补充：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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。