Postgresql关于日期和时间戳转换问题

前端之家收集整理的这篇文章主要介绍了Postgresql关于日期和时间戳转换问题前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

pg中存储的是日期,形如:"2017-01-06 14:38:44"

查询的时候要以时间戳的形式返回,形如:1483713524

查询,epoch函数可以实现此功能,例如一下sql

SELECT	
		extract(epoch FROM valid_end_time) * 1000,valid_end_time as valid_end_time
		FROM  cms_policy_access
  		WHERE valid_flag = 1 and type = '00101'

查出的结果如下:

可以看到,1483713524000就是此时间的一个时间戳格式(扩大了1000倍后)

但是可能存在一个问题,时区问题,比如这里的1483713524000,转化为时间格式之后为:

2017-01-06 22:38:44

与存储的时间相比,增大了八个小时。

猜测原因可能是:

此表cms_policy_access中的valid_end_time的格式是timestamp without time zone,epoch是根据其他时区计算的时间戳,和本地的时区不同,导致了时间的间隔;

百度pg的手册,里面是使用EPOCH FROM TIMESTAMP WITH TIME ZONE的方式转换的,比如:

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2017-01-05 14:38:42');

得到1483598322

经过转换得到的时间是准确的,两者的差别就是TIMESTAMP WITH TIME ZONE

关键是上述的sql改为下面形式就报错

SELECT	
		extract(epoch FROM TIMESTAMP WITH TIME ZONE valid_end_time),valid_end_time as valid_end_time
		FROM  cms_policy_access
  		WHERE valid_flag = 1 and type = '00101'

各种形式的语法都试了,还是没用

继续百度,终于得到另外一个方法,直接贴sql

SELECT	
		cast(extract(epoch FROM date_trunc('second',to_timestamp(to_char(valid_start_time,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')))* 1000 as text) as "starttime",extract(epoch FROM valid_start_time) * 1000,valid_start_time as valid_start_time
		FROM  cms_policy_access
  		WHERE valid_flag = 1 and type = '00101'

可以看到,这里两者获取的时间戳确实不一样,经过转换,发现第一列是正确的。。。

其他相关的可以百度

----------------------------------------------

时间戳转换:

update cms_products_base_info set 
sale_start_date = to_timestamp('2017-02-01 13:22:11',sale_END_date = to_timestamp('2017-04-13 16:22:33','YYYY-MM-DD HH24:MI:SS')  
where product_code = 'YLX'
原文链接:https://www.f2er.com/postgresql/194152.html

猜你在找的Postgre SQL相关文章