timestamptz查询结果用时间戳显示

这个方案目前还有很多问题,我个人认为是因为我还有很多细节处没有参透,所以结果很怪异。

先说明一下我的需求,当我开发了一个分页接口,我需要测试其中的几个时间相关的字段,比如记录创建时间在某个范围,我一般会先看看我库中有哪些数据,然后直接把根据这些数据造测试数据:

2021-10-18-14-29-25

但是,由于我的接口是用时间戳的,而我直接查库得到的是一个时间,所以在使用的时候并不是很方便,我需要找另一个转换工具将这个时间转换为时间戳。

我目前找到了如下查询语句的写法,可以稍微简化一下我的步骤:

1
2
3
4
5

select EXTRACT(epoch FROM gmt_create_time)
from t_common_material
order by gmt_create_time;

但是查询结果仍然不是太满意,我需要拷贝整数部分,然后手动加上三个零,才能得到一个稍微可以用的时间戳:

2021-10-18-14-33-35

该方案目前存在的问题有下:

  1. 查询的即使是时间,也是不准确的,我11左右插入的记录,但是查出来的数据显示是3点,我怀疑是我DataGrip的时间设置的有问题。

  2. 整数部分精确到了秒,但是我实际上需要的是一个精确到毫秒的时间戳。

问题一

确实是DataGrip时区设置的问题,相关的笔记我会整理在DataGrip分类下。

问题二(我暂时还没验证,大概看了一下差不多)

优化一下查询语句:

1
2
3
4

select floor(extract(epoch from ((gmt_create_time - timestamp '1970-01-01 00:00:00') * 1000)))
from t_common_material;

参考资料

  1. postgresql获取系统当前时间毫秒数的sql,以及秒级时间戳

  2. Postgresql中时间戳与日期的相互转换(同样适用于GreenPlum)