【Java】oracle与pgsql环境下date与timestamp类型的差异比较

一、问题引入
近期在项目中发现，实体中创建时间这一字段被定义成Timestamp，而在pg环境下，数据库中的创建时间字段类型为date，该字段本该存放年月日时分秒格式的内容，入库后却成了年月日格式，由于实体通过@JSONField注解指定返回的格式为年月日时分秒，前端回显展示数据时将时分秒的位置都补0展示(例如:2023-04-30 00:00:00)。此时在oracle环境上发现年月日时分秒回显的数据正常……

//对象实体中本意希望能在前端展示年月日时分秒格式的日期值，实体中该字段一开始就被定义为Timestamp类型
//此时的oracle、pg数据库该字段被定义成date类型
@Column(name = "D_CREATE_TIME")
@JSONField(format = "yyyy-MM-dd HH:mm:ss")
public Timestamp createTime;
1
2
3
4
5

似乎date与timestamp存在着精度上的差异……

二、先看看oracle这边的情况

①date类型是Oracle常用的日期型变量，时间间隔是秒。两个日期型相减得到是两个时间的间隔，其单位是“天”。

②timestamp是date类型的扩展，可以精确到小数秒（fractional_seconds_precision），可以是 0 to 9，缺省值为６。两个timestamp相减，不能直接得到天数，而是得到多少天多少小时多少秒。

date和timestamp之间的相互转换

三、直接在两个数据库中测试
①在oracle上的测试

SELECT  to_date('2023-04-30 12:12:12','yyyy-MM-dd hh24:mi:ss') from dual;
//结果为：2023/4/30 12:12:12

SELECT  to_timestamp('2023-04-30 12:12:12','yyyy-MM-dd hh24:mi:ss') from dual;
//结果为:30-4月 -23 12.12.12.000000000 下午
1
2
3
4
5

②在pgsql上的测试

SELECT  to_date('2023-04-30 12:12:12','yyyy-MM-dd hh24:mi:ss') ;
//结果为:2023-04-30

SELECT  to_timestamp('2023-04-30 12:12:12','yyyy-MM-dd hh24:mi:ss') ;
//结果为:2023-04-30 12:12:12
1
2
3
4
5

这里就看到了pg环境下，年月日时分秒的日期数据被处理成年月日了……

四、问题解决
在上述的差异分析后，项目中决定将三种数据库该字段统一定义为TIMESTAMP，由此解决年月日时分秒的显示问题。

--oracle
ALTER TABLE T_DATA MODIFY D_CREATE_TIME TIMESTAMP;

--pgsql
ALTER TABLE public.T_DATA ALTER COLUMN D_CREATE_TIME TYPE TIMESTAMP USING  D_CREATE_TIME::TIMESTAMP;

--mysql
alter table T_DATA modify D_CREATE_TIME timestamp;
1
2
3
4
5
6
7
8