SQL 日期时间转换问题

问题一：非正常时间格式转换问题

背景

最近在项目上，使用ETL工具配置任务，将上游数据直接推送到kafka topic 中，让另一个系统去消费这些实时成交的数据。

之前经验的不足，没有对推送的数据做限制，导致每次推送到topic 里都是全量数据，几百万的数据被反复推送到topic里。需要对推送的任务进行修改，增量推送数据到kafka，经过咨询，使用单调递增的流水字段去配置，推送过的数据就不再反复推送。

并且为了避免数据的重复，还需要设置主键，确保每一条数据都是为唯一。

问题

我使用每笔交易的时间去做流水字段，简单的进行了to_date()，发现报错了：非法的时间日期类型数据。

查看上游的数据，交易时间为：

我们想要的时间是 15:05:17 09:31:58 时分秒格式的，并且另一个系统所需要的为字符串类型

解决

首先，对于10点之前的数据肯定要在前边补充一个0，让它是正常的时间数据格式，使用lpad 函数

然后再将利用to_date 进行转换

to_date 转换是默认加上了年月日的，还需要再进行一次to_char转换，转换为需要用到的字符串格式

问题二：上下游编码不一致产生的日期格式转换问题

背景

在某合规系统，利害关系人的数据源变更，我们采集的上游由数据库A变更为数据库B，原先写的SP转换突然就报错了，其中有一段日期转换出错了，经过查看采集过来的数据，发现B库的日期格式与A库的日期格式不一致。

原先A库的日期格式是YYYY-MM-DD HH24.MI.SS.FF

但是变更源库后，表的日期格式是YY-MON-DD HH.MI.SS.FF9 PM的格式

问题

原先，我们系统里，只需要将日期做如下的转换即可

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


  insert into CMSCONFIG.T_XXX_REPORT_XXX
    (app_no ,
     app_emp_no ,
     emp_no ,
     report_type2  ,
     status  ,
     app_time ,
     approve_time
     )
  select a.id,
         a.brokerid,
         a.brokerid,
         '1',
         '1',
         to_date(to_char(to_timestamp(a.updatetime,'yyyy-mm-dd hh24:mi:ss.ff'),'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss'),
         to_date(to_char(to_timestamp(a.updatetime,'yyyy-mm-dd hh24:mi:ss.ff'),'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss')
    from NC11.ST_EM_XXXXXXON a
   where not exists (select 1 from CMSCONFIG.T_XXX_REPORT_XXX t
                      where a.id = t.app_no
                        and t.report_type2 = '1');

1
2
3
4
5


select to_date(to_char(to_timestamp('2024-6-1 17.38.54.000000000',
                                    'yyyy-mm-dd hh24:mi:ss.ff'),
                       'yyyy/mm/dd hh24:mi:ss'),
               'yyyy/mm/dd hh24:mi:ss') as datetime
  from dual;

但是换库之后，日期格式改变了，变为了如下形式：

1

'18-MAR-24 03.44.12.000000000 PM'

此时，还是继续使用上述的SQL进行日期转换，则会报错无效的月份

当时尝试了很多

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 在Oracle中报错日期格式无法识别
SELECT TO_DATE('18-MAR-24 03.44.12.000000000 PM',
               'YY-MON-DD HH.MI.SS.FF9 PM') AS datetime
  FROM dual;


-- 在Oracle中报错无效的月份
SELECT TO_TIMESTAMP('18-MAR-24 03.44.12.000000000 PM',
                    'YY-MON-DD HH.MI.SS.FF9 PM') AS datetime
  FROM dual;


-- 在Oracle中报错要求AM/A.M.或PM/P.M.
SELECT TO_TIMESTAMP('18-3-24 03.44.12.000000000 PM',
                    'YY-MM-DD HH.MI.SS.FF9 PM') AS datetime
  FROM dual;

到底应该如何将 ‘18-MAR-24 03.44.12.000000000 PM’ 转换为 ‘2024/3/18 15:44:12’ 呢？？？

当时百度了很多路子，试过很多方法，在一篇帖子上看到可能跟NLS_LANGUAGE有关，随后去ORACLE官网看相关解答：

语言环境的不一致可能会导致数据不一致，有可能这个原因

解决

有了方向后，开始尝试

查看本地库的NLS_LANGUAGE参数

上游的库NLS_LANGUAGE，让客户帮忙咨询，是american

在本地尝试

1
2
3
4
5
6


select to_date(
                to_char(
                       to_timestamp('18-MAR-24 03.44.12.000000000 PM', 'DD-MON-RR HH.MI.SS.FF AM', 'NLS_DATE_LANGUAGE = AMERICAN'), 
                'YYYY/MM/DD HH24:MI:SS'),
                                       'YYYY/MM/DD HH24:MI:SS') AS datetime
  from dual;

可以转换出结果，没问题。就是这样，在to_timestamp里加入NLS_LANGUAGE=AMERICAN 参数可以解决。

把这段逻辑替换到SP对应的代码里，成功解决日期报错的问题。