postgres的时间转换

天下苦postgres时间转换久已!

最近在操作数据库时,遇到频繁的时间操作,每次弄完了就忘了,今天痛定思痛,下定决心(终于自己也受不了自己的lazy了)对postgres的时间操作进行一下总结。本文竟可能详尽的记录postgres中涉及到date、timestamp以及和字符串之间的转换进行说明。

1.utc对timestamp的转换

在postgres中没有utc这个概念,为什么?这个问题没有深入的调研,没有什么我们不关心,但是与之对应的有一个概念——epoch,其意思为新纪元,新时代,怎么定义的新呢,计算机上面就是指1970-01-01 00:00:00之后的时间,有人会说这不就是utc吗,好吧,我承认买了个关子,但是有助于记忆。

postgres中的时间可以精确到小数点后五位,也就是10微秒的单位,已经很小了,我们先来感受一下在pg中获取epoch:

--把时间戳转成epoch值
postgres=# select extract(epoch from now());
date_part     
---
1533803404.49598
(1 row)

上面是将当前时间戳转化为epoch,转换的过程中主要用到extract函数,

--将具体时间戳转换为utc
select extract(epoch from timestamp without time zone '1970-01-01 01:00:00');
 date_part 
-----------
  3600
(1 row)
--根据具体时间进行计算,一个小时的utc
select extract(epoch from interval '+1 hours');
 date_part 
-----------
  3600
(1 row)
--同上
select extract(epoch from interval '-1 hours');
 date_part 
-----------
  -3600
(1 row)

下面是将epoch值也就是utc值,转换为时间戳,如下:

--把epoch 值转换回时间戳
postgres=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1447898857.74524 * INTERVAL '1 second';           
?column?           
---
2015-11-19 10:07:37.74524+08

其他都很好理解,这里有一点需要注意,就是with time zone,字面意思很好理解,就是带时区,如果不带时区怎么表示?对了就是without time zone,我们将上面语句进行不带时区的运行,如下:

SELECT TIMESTAMP WITHout TIME ZONE 'epoch' +  1447898857 * INTERVAL '1 second';
---
2015-11-19 02:07:37

整整相差了8个小时,不卖关子,其主要区别如下:

没有时区代表的是绝对时间,absolute timestamp,即 UTC (UTC+0) 时间。

带着时区的代表相对时间,relative timestamp,即当地时间,如北京的当地时间是 UTC+8 的时间。

使用的一个最佳实践是时间类型都设为 timestamp with time zone 类型,只有在根据 timestamp 进行 partition 时才使用 timestamp without time zone 类型,因为 partition 必须使用 immutable 数据 (即在任何情况下数据取出来都一样),而 timestamp with time zone 的数据值与 postgres 配置的 timezone 有关。

这两种数据类型的区别是:

  • 以当地时间存储数据到 timestamp with time zone 类型的字段时,postgres 底层会以 UTC 时间存储,展示数据时会根据 postgres 设置的 timezone 显示为当时时间。
  • 以当地时间存储数据到 timestamp without time zone 类型的字段时,postgres 底层以输入的数据进行存储,展示时会原样展示,与 postgres 设置的时区无关。

查看当前时间戳

下面这是查看当前时间戳的几种方式,如下:

 --查看当前的时间戳,还有函数now()

postgres=# select clock_timestamp(),current_timestamp,localtimestamp;

        clock_timestamp        |              now              |         timestamp          

-------------------------------+-------------------------------+----------------------------

 2016-02-02 17:54:15.547194+08 | 2016-02-02 17:54:15.546956+08 | 2016-02-02 17:54:15.546956

clock_timestamp和current_timestamp都有时区信息,而localtimestamp没有时区信息

时间戳加减

--时间加减
postgres=# select date '2016-02-02 10:00:00'+ interval '10 minutes'; 
?column?       
---
 2016-02-02 00:10:00

时间戳格式转化

将时间戳根据具体格式转换为字符串:

 SELECT to_char((TIMESTAMP WITH TIME ZONE'epoch' + 1447898857 * INTERVAL '1 second' ),'yyyy-MM');
 2015-11

时间段内的随机时间

--直接用sql生成随机日期时间

select '2015-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval; 

random()生成一个0到1的随机数,trunc()对生成随机数进行截取,剩下的参考时间戳加减过程的相关做法。

--创建随机日期时间函数       
   CREATE OR REPLACE FUNCTION rand_date_time(start_date date, end_date date) RETURNS TIMESTAMP AS  
BODY  
 DECLARE  
    interval_days integer;  
    random_seconds integer;  
    random_dates integer;  
    random_date date;  
    random_time time;
BEGIN  
    interval_days := end_date - start_date;  
    random_dates:= trunc(random()*interval_days);
    random_date := start_date + random_dates; 
    random_seconds:= trunc(random()*3600*24); 
    random_time:=' 00:00:00'::time+(random_seconds || ' second')::INTERVAL;
    RETURN random_date +random_time;  
END;   
BODY  
LANGUAGE plpgsql;  
--生成指定时间内的随机时间
SELECT rand_date_time('2000-01-01', '2013-12-31');  

不同时区日期转化

--不同时区之间的转换,pst美国太平洋标准时间

postgres=# select '2016-02-03 09:07:30.816885+08' at time zone 'pst';
timezone          
---
 2016-02-02 17:07:30.816885
(1 row)
--cct中国沿海时间
postgres=# select '2016-02-03 09:07:30.816885+08' at time zone 'cct';
timezone          
---
 2016-02-03 09:07:30.816885
(1 row)
--将西五区转到东八区
postgres=#  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'cct';
      timezone       
---
 2001-02-17 09:38:40

系统时区查看

 --查看系统支持的时区
 select * from pg_timezone_names ; 

系统时区设置

--时区设置参数
timezone = 'PRC'
--修改时区的方法

1. 全局参数
   postgresql.conf
   timezone='UTC'
2. 数据库级配置
    alter database dbname set timezone='UTC';
    pipeline=# select * from pg_db_role_setting ;

 setdatabase | setrole |              setconfig               

-------------+---------+--------------------------------------

       14930 |       0 | {TimeZone=UTC}

3. 用户级配置
   alter role rolname set timezone='UTC';
   或者
   alter role all set timezone='UTC';

    pipeline=# select * from pg_db_role_setting ;

 setdatabase | setrole |              setconfig               

-------------+---------+--------------------------------------

       14930 |       0 | {TimeZone=UTC}
           0 |       0 | {TimeZone=UTC}

执行休眠时间设置

--休眠1.5秒后执行,单位秒
SELECT clock_timestamp(),pg_sleep(1.5),clock_timestamp();

--休眠5分钟,单位interval
SELECT clock_timestamp(),pg_sleep_for('5 minutes'),clock_timestamp();

--到指定时间执行,注意这些休眠时间不是完全精确的
SELECT clock_timestamp(),pg_sleep_until('today 10:00'),clock_timestamp();

字符串到时间戳和日期

主要涉及到的函数为todate和toTimestamp,示例如下:

select to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') 
--转化为日期
2018-03-12
  --对日期进行比较,输出boolean
  select current_timestamp <= to_date('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss')
f
select current_timestamp <= to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss') flag;
select to_timestamp('2018-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss')
---
f
2018-03-12 18:47:35+08

从上面比较可以看出,date和timestamp的主要区别,需要特别说明的一点是,在指定日期的格式的时候,特别注意时分秒的格式指定问题(使用惯了java的格式,用这个会有点不习惯)。

  • 2
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值