达梦数据时间格式处理

使用案例

软件	版本
操作系统	Redhat 7 及以上版本
DM 数据库	DM 8.0 及以上版本
CPU 架构	x86、ARM、龙芯、飞腾等国内外主流 CPU

业务场景一

近期业务数据有了修改，创建时间做了变动，需要将创建时间、执行时间改成符合业务环境的时间，因而需要通过对数据库时间字段进行加减操作。

实现方法

NUMTODSINTERVAL()

可以通过NUMTODSINTERVAL()函数完成在时间字段上的增减操作

TO_CAHR()

• 在当前时间基础上减1小时30分钟

UPDATE DATAFLAT.DB_WORK_ORDER SET create_time = start_time+NUMTODSINTERVAL(-1,'hour')+NUMTODSINTERVAL(-30,'minute')
WHERE id IN (
    SELECT id FROM DATAFLAT.DB_WORK_ORDER WHERE order_name LIKE '????????-%' 
);

• 对日志清单上的日期进行修改

UPDATE DATAFLAT.DB_ORDER_TRANS_INFO A SET
create_time = TO_TIMESTAMP(
    TO_CHAR((SELECT start_time FROM DATAFLAT.DB_WORK_ORDER B WHERE A.order_id = B.id),'YYYY-MM-DD') ||' '||
    TO_CHAR(A.update_time,'HH24:MI:SS.FF6')
)
WHERE A.order_id IN (
    SELECT id FROM DATAFLAT.DB_WORK_ORDER WHERE order_name LIKE '????????-%' 
)

• 修改日志清单上的第一条时间与创建时间一致

UPDATE DATAFLAT.DB_ORDER_TRANS_INFO A SET
create_time = (SELECT create_time FROM DATAFLAT.DB_WORK_ORDER B WHERE A.order_id = B.id)
WHERE A.status = 0 AND A.order_id IN (
    SELECT id FROM DATAFLAT.DB_WORK_ORDER WHERE order_name LIKE '????????-%'
);

• 修改创建时间为每月1号时间

UPDATE DATAFLAT.DB_WORK_ORDER A SET create_time = TO_TIMESTAMP(
    TO_CHAR((SELECT start_time FROM DATAFLAT.DB_WORK_ORDER B WHERE A.id = B.id),'YYYY-MM') || '-01' ||' '||
    TO_CHAR(A.update_time,'HH24:MI:SS.FF6')
)
WHERE id IN (
    SELECT id FROM DATAFLAT.DB_WORK_ORDER WHERE order_name LIKE '????????-%' AND create_user_id = '1689888888885385'
);

UPDATE DATAFLAT.DB_ORDER_TRANS_INFO A SET
create_time = TO_TIMESTAMP(
    TO_CHAR((SELECT start_time FROM DATAFLAT.DB_WORK_ORDER B WHERE A.order_id = B.id),'YYYY-MM') || '-01'  ||' '||
    TO_CHAR(A.update_time,'HH24:MI:SS.FF6')
)
WHERE STATUS IN ('1','7') AND A.order_id IN (
    SELECT id FROM DATAFLAT.DB_WORK_ORDER WHERE order_name LIKE '????????-%' AND create_user_id = '1689888888885385'
);

业务场景二

之前接入的数据格式都是通过文本格式接入进来，现在需要对这些数据进行治理，就需要将字段类型改成相适应的字段类型。大致浏览了一下，这些格式有unix时间戳格式、20230101数字串

实现方法

FROM_UNIXTIME(unix_timestamp, format)

分享个好用的时间戳转换工具：

时间戳转换工具(Unix timestamp) - 在线工具 (tools.fun)

• 将存储时间戳的字段，通过from_unixtime()转换成可读日期

ALTER TABLE MODEL.TABLE_TEST1 ADD UPDATETIME TIMESTAMP(6);
UPDATE MODEL.TABLE_TEST1 SET UPDATETIME = TO_TIMESTAMP(FROM_UNIXTIME(SJ,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS');

• 也遇到这种时间格式Thu Aug 27 18:05:49 CST 2015，通过TO_DATE()和REPLACE完成

ALTER TABLE MODEL.TABLE_TEST2 ADD UPDATETIME TIMESTAMP(6);
UPDATE MODEL.TABLE_TEST2 A SET A.UPDATETIME = TO_DATE(REPLACE(UPDATETIME,'CST',''),'DY MON DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE = AMERICAN');

常见函数

处理时间格式时常会用到“数值函数”，“字符串函数”，“日期时间函数”等

表1 数值函数

序号	函数名	功能简要说明
01	ABS(n)	求数值 n 的绝对值
02	ACOS(n)	求数值 n 的反余弦值
03	ASIN(n)	求数值 n 的反正弦值
04	ATAN(n)	求数值 n 的反正切值
05	ATAN2(n1,n2)	求数值 n1/n2 的反正切值
06	CEIL(n)	求大于或等于数值 n 的最小整数
07	CEILING(n)	求大于或等于数值 n 的最小整数，等价于 CEIL(n)
08	COS(n)	求数值 n 的余弦值
09	COSH(n)	求数值 n 的双曲余弦值
10	COT(n)	求数值 n 的余切值
11	DEGREES(n)	求弧度 n 对应的角度值
12	EXP(n)	求数值 n 的自然指数
13	FLOOR(n)	求小于或等于数值 n 的最大整数
14	GREATEST(n {,n})	求一个或多个数中最大的一个
15	GREAT (n1,n2)	求 n1、n2 两个数中最大的一个
16	LEAST(n {,n})	求一个或多个数中最小的一个
17	LN(n)	求数值 n 的自然对数
18	LOG(n1[,n2])	求数值 n2 以 n1 为底数的对数
19	LOG10(n)	求数值 n 以 10 为底的对数
20	MOD(m,n)	求数值 m 被数值 n 除的余数
21	PI()	得到常数 π
22	POWER(n1,n2)/POWER2(n1,n2)	求数值 n2 以 n1 为基数的指数
23	RADIANS(n)	求角度 n 对应的弧度值
24	RAND([n])