Convert Oracle Date into RFC 3339 Format

1
votes

I have a date field in oracle which returns

17-APR-19 12:00:00 AM

I also have a time column (VARCHAR) which returns HHMM in Military

1810

I'd like to combine these two fields to create a timestamp that is formatted to RFC 3339 standards. Preferable like this.

2019-04-17T18:10:00Z

I can convert a timestamp into the correct time using this:

SELECT
    TO_CHAR(
        SYSTIMESTAMP AT TIME ZONE 'UTC',
        'yyyy-mm-dd"T"hh24:mi:ss"Z"'
    )
FROM dual;

Is there a way to convert my date and time field into this timestamp format? The time on the date field is incorrect and needs to be replaced by the time field.

3
sqloracle

3 Answers

1
votes

You can TRUNCate your date back to midnight and then use NUMTODSINTERVAL to add hours and minutes to it to get the correct time component:

Oracle Setup:

CREATE TABLE your_table ( your_date_column, your_time_column ) AS
  SELECT DATE '2019-04-17', '1810' FROM DUAL

Query:

SELECT TO_CHAR(
         TRUNC( your_date_column )
         + NUMTODSINTERVAL( SUBSTR( your_time_column, 1, 2 ), 'HOUR' )
         + NUMTODSINTERVAL( SUBSTR( your_time_column, 3, 2 ), 'MINUTE' ),
         'YYYY-MM-DD"T"HH24:MI:SS"Z"'
       ) AS combined_date_time
FROM   your_table

Output:

| COMBINED_DATE_TIME   |
| :------------------- |
| 2019-04-17T18:10:00Z |

db<>fiddle here

If you want the value as a TIMESTAMP WITH TIME ZONE then:

SELECT CAST(
         TRUNC( your_date_column )
         + NUMTODSINTERVAL( SUBSTR( your_time_column, 1, 2 ), 'HOUR' )
         + NUMTODSINTERVAL( SUBSTR( your_time_column, 3, 2 ), 'MINUTE' )
         AS TIMESTAMP
       ) AT TIME ZONE 'UTC' AS combined_date_time
FROM   your_table
0
votes

Just do a bit of string concatenation

to_char( your_date, 'yyyy-mm-dd' ) || 
  'T' ||
  substr( your_time, 1, 2 ) ||
  ':' ||
  substr( your_time, 3, 2 ) ||
  ':00Z'

assuming that your_time is always 4 characters long (i.e. 2 AM is represented as the string '0200' rather than '200'). This also assumes that the seconds will always be '00'.

0
votes

You can achieve this by converting your_number into minutes and add it to your date, then cast it to timestamp as following:

SELECT CAST(
         your_date + 
         (FLOOR(YOUR_TIME/100)*60 + MOD(YOUR_TIME,100)) / 1440
         AS TIMESTAMP
       ) AT TIME ZONE 'UTC' AS YOUR_TIME_STAMP
FROM   your_table;

Cheers!!