Eg: One row in one column contains below data.I need to convert the unix timestamps to actual datetime in oracle.Please provide sql query to convert. NOTE: {EOT} is special character End of Transmission. {ETX} is special character End of Text.
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
Assuming that the times are all at the start of each line within the string then you can use a recursive subquery-factoring clause to iterate over each sub-string line within the string and regular expressions to find each time at the start of that line and then all you need to do is add the offset interval to the epoch the time is being measured from (i.e. 1970-01-01):
Oracle Setup:
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;
Query:
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;
Output:
ID | TIME | DESCRIPTION -: | :------------------ | :---------------------------------------------------------------- 1 | 2019-02-15 10:23:33 | Bharath 1 | 2019-02-15 10:29:19 | LakshmanUpdate to Current Summary: Under Investigation 1 | 2019-02-15 10:51:58 | SitaUpdate to Current Summary: Outage restored- Under Observation 1 | 2019-02-16 07:12:56 | Rama 2 | 1970-01-01 00:00:00 | ABC 3 | null | null 4 | 2009-02-13 23:31:30 | A 4 | 2009-02-13 23:31:31 | B 4 | 2009-02-13 23:31:32 | C
db<>fiddle here
Update:
Oracle Setup:
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;
Query:
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;
Output:
ID | TIME | DESCRIPTION
-: | :------------------ | :-------------------------------------------------------------------------
1 | 2019-02-15 10:23:33 | Bharath<br>testtest
1 | 2019-02-15 10:29:19 | LakshmanUpdate to Current Summary: {EOT}Under Investigation{EOT}<br>suresh
1 | 2019-02-15 10:51:58 | itaUpdate to Current Summary: {EOT}Outage restored- Under Observation<br>
1 | 2019-02-16 07:12:56 | Rama
2 | 1970-01-01 00:00:00 | ABC
3 | null | null
4 | 2009-02-13 23:31:30 | A
4 | 2009-02-13 23:31:31 | B
4 | 2009-02-13 23:31:32 | C
db<>fiddle here
