sql - 在Oracle的一列数据中随机转换具有unix时间戳的数据

Question

例如：一列中的一行包含以下数据。我需要将 unix 时间戳转换为 oracle 中的实际日期时间。请提供 sql 查询进行转换。注意：{EOT} 是特殊字符传输结束。{ETX} 是特殊字符的文本结尾。

1550226213{EOT}Bharath
testtest{ETX}1550226559{EOT}LakshmanUpdate to Current Summary: {EOT}Under Investigation{EOT}
suresh{ETX}1550227918S{EOT}itaUpdate to Current Summary: {EOT}Outage restored- Under Observation
{ETX}1550301176{EOT}Rama

标签： sqloracleoracle11ginformatica-powercenter

Answer 1

假设时间都在字符串中每一行的开头，那么您可以使用递归子查询因子子句迭代字符串中的每个子字符串行，并使用正则表达式在该行的开头找到每个时间和那么您需要做的就是将偏移间隔添加到测量时间的纪元（即 1970-01-01）：

甲骨文设置：

CREATE TABLE test_data ( id, value ) AS
SELECT 1, '1550226213Bharath
1550226559LakshmanUpdate to Current Summary: Under Investigation
1550227918SitaUpdate to Current Summary: Outage restored- Under Observation
1550301176Rama' FROM DUAL UNION ALL
SELECT 2, '0ABC' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, '1234567890A
1234567891B
1234567892C' FROM DUAL;

查询：

WITH lines ( id, value, unix_time, description, line_no, total_lines ) AS (
  SELECT id,
         value,
         TO_NUMBER( REGEXP_SUBSTR( value, '^\d+', 1, 1, 'm' ) ),
         REGEXP_SUBSTR( value, '^\d+(.*)$', 1, 1, 'm', 1 ),
         1,
         COALESCE( REGEXP_COUNT( value, '^\d+', 1, 'm' ), 0 )
  FROM   test_data
UNION ALL
  SELECT id,
         value,
         TO_NUMBER( REGEXP_SUBSTR( value, '^\d+', 1, line_no + 1, 'm' ) ),
         REGEXP_SUBSTR( value, '^\d+(.*)$', 1, line_no + 1, 'm', 1 ),
         line_no + 1,
         total_lines
  FROM   lines
  WHERE  line_no < total_lines
)
SELECT id,
       DATE '1970-01-01' + unix_time * INTERVAL '1' SECOND AS time,
       description
FROM   lines
ORDER BY id, line_no;

输出：

身份证 | 时间 | 描述                                                      
-: | :----------------- | :------------------------------------------------ ---------------
 1 | 2019-02-15 10:23:33 | 巴拉特                                                          
 1 | 2019-02-15 10:29:19 | Lakshman 更新到当前摘要：正在调查中           
 1 | 2019-02-15 10:51:58 | SitaUpdate 到当前摘要：已恢复中断 - 观察中
 1 | 2019-02-16 07:12:56 | 拉玛                                                             
 2 | 1970-01-01 00:00:00 | 美国广播公司                                                              
 3 | 空                | 无效的                                                             
 4 | 2009-02-13 23:31:30 | 一个                                                                
 4 | 2009-02-13 23:31:31 | 乙                                                                
 4 | 2009-02-13 23:31:32 | C                                                                

db<>在这里摆弄

---

更新：

甲骨文设置：

CREATE TABLE test_data ( id, value ) AS
SELECT 1, '1550226213{EOT}Bharath
testtest{ETX}1550226559{EOT}LakshmanUpdate to Current Summary: {EOT}Under Investigation{EOT}
suresh{ETX}1550227918{EOT}itaUpdate to Current Summary: {EOT}Outage restored- Under Observation
{ETX}1550301176{EOT}Rama' FROM DUAL UNION ALL
SELECT 2, '0{EOT}ABC' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, '1234567890{EOT}A{ETX}1234567891{EOT}B{ETX}1234567892{EOT}C' FROM DUAL;

查询：

WITH lines ( id, value, line, line_no, total_lines ) AS (
  SELECT id,
         value,
         REGEXP_SUBSTR( value, '(.+?)(\{ETX\}|$)', 1, 1, 'n', 1 ),
         1,
         COALESCE( REGEXP_COUNT( value, '(.+?)(\{ETX\}|$)', 1, 'n' ), 0 )
  FROM   test_data
UNION ALL
  SELECT id,
         value,
         REGEXP_SUBSTR( value, '(.+?)(\{ETX\}|$)', 1, line_no + 1, 'n', 1 ),
         line_no + 1,
         total_lines
  FROM   lines
  WHERE  line_no < total_lines
)
SELECT id,
       DATE '1970-01-01' + TO_NUMBER( REGEXP_SUBSTR( line, '^(\d+)(\{EOT\}|$)', 1, 1, 'n', 1 ) ) * INTERVAL '1' SECOND AS time,
       REGEXP_SUBSTR( line, '^(\d+)\{EOT\}(.*)$', 1, 1, 'n', 2 ) AS description
FROM   lines
ORDER BY id, line_no;

输出：

身份证 | 时间 | 描述                                                               
-: | :----------------- | :------------------------------------------------ ----------------------
 1 | 2019-02-15 10:23:33 | 巴拉特<br>testtest                                                       
 1 | 2019-02-15 10:29:19 | Lakshman 更新到当前摘要：{EOT}正在调查{EOT}<br>suresh
 1 | 2019-02-15 10:51:58 | itaUpdate 到当前摘要：{EOT}已恢复中断 - 观察中<br>
 1 | 2019-02-16 07:12:56 | 拉玛                                                                      
 2 | 1970-01-01 00:00:00 | 美国广播公司                                                                       
 3 | 空                | 无效的                                                                      
 4 | 2009-02-13 23:31:30 | 一个                                                                         
 4 | 2009-02-13 23:31:31 | 乙                                                                         
 4 | 2009-02-13 23:31:32 | C                                                                         

db<>在这里摆弄