4 Replies Latest reply on Jun 25, 2019 11:21 AM by DavidGaskell

    Date Formats when using JSON_OBJECT sql function

    DavidGaskell

      Hi,

       

      I was wondering whether anyone has experience of using JSON_OBJECT SQL functions on 12.2.0.1.0, mainly surrounding using DATE fields in a query.

       

      I have provided a simplified example to demonstrate the issue:

       

      We have a simple table of the following structure:

       

      PUBLIC_HOLIDAYS:
      
      Name         Null     Type         
      ------------ -------- ------------ 
      PH_SEQ       NOT NULL NUMBER(10)   
      HOLIDAY_DATE NOT NULL DATE         
      DESCRIPTION           VARCHAR2(50)
      

       

       

      Setting the session settings as follows to demonstrate the problem:

       

      alter session set nls_timestamp_format = 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"';
      alter session set nls_date_format = 'DD-MON-YYYY';

       

      When querying the data using a standard select, as per:

       

      select  cast (holiday_date as timestamp)
              ,holiday_date
      from public_holidays;
      

       

      The output is shown as follows (this is as I would expect):

       

      2020-05-04T00:00:00.000Z      04-MAY-2020
      2020-05-25T00:00:00.000Z      25-MAY-2020
      

       

      However when applying a JSON_OBJECT function to convert to JSON, the dates are manipulated as follows e.g. the Z is now omitted from both:

       

      select JSON_OBJECT('dateWithTz' VALUE cast (holiday_date as timestamp),
                         'regDate' VALUE holiday_date)
      from public_holidays;
      
      {"dateWithTz":"2020-05-04T00:00:00","regDate":"2020-05-04T00:00:00"}                                                                                                                                                                                                                                                                                                                                          {"dateWithTz":"2020-05-25T00:00:00","regDate":"2020-05-25T00:00:00"}
      

       

      I essentially want to return the date as a JSON object, with the date formatted as per the ISO 8601 format. e.g. 2019-06-20T15:41:17.000Z

       

      The oracle documentation suggests this is how it should work; however as above we see the 'Z' omitted e.g. Timezone for a zero offset. Would this be expected?

       

      FYI the oracle documentation is:https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-C0F8F837-EE36-4EDD-9261-6E8A9…

       

      "A DATE or TIMESTAMP value is converted to ISO 8601 format, and the result is enclosed in double quotation marks (")."

       

      The only workaround I can see is TO_CHAR the date and apply the required format e.g. YYYY-MM-DD"T"HH24:MI:SS"Z"; however I was hoping this could have been handled by default.

       

      If anyone has any comments it would be appreciated.

       

      Kind Regards

      David

        • 1. Re: Date Formats when using JSON_OBJECT sql function
          cormaco

          I ran this query in Oracle 18XE and got a result including "Z", so maybe this is a bug in your Oracle version:

           

          select sessiontimezone, sysdate, cast(sysdate as timestamp with time zone) at time zone 'UTC' sysdate_in_UTC, json_object('ts' value systimestamp) json from dual;
          
          
          SESSIONTIMEZONE      SYSDATE             SYSDATE_IN_UTC                    JSON                                    
          -------------------- ------------------- --------------------------------- ----------------------------------------
          Europe/Berlin        20.06.2019 19:28:17 20.06.2019 17:28:17.000000000 UTC {"ts":"2019-06-20T17:28:17.464955Z"}    
          
          
          

           

          The only workaround I can see is TO_CHAR the date and apply the required format e.g. YYYY-MM-DD"T"HH24:MI:SS"Z"; however I was hoping this could have been handled by default.

          You can't just append "Z" to any timestamp value, it has be in timezone UTC. See my example above how to convert a date value to a UTC timestamp

          • 2. Re: Date Formats when using JSON_OBJECT sql function
            dmcmahon-Oracle

            Your example worked because you used TIMESTAMP WITH TIME ZONE. The thread author cast his date to the TIMESTAMP type, which doesn't have a zone.

            • 3. Re: Date Formats when using JSON_OBJECT sql function
              dmcmahon-Oracle

              Please try casting to TIMESTAMP WITH TIME ZONE if you want a zone included in the output.

               

              I should note that the supported input types are limited in each release. The timestamp with time zone type isn't documented as a supported input type until release 18c. Support for it in 12.2 isn't documented, and it may work only with certain patch levels.

               

              The reliable way to ensure the exact format you want is to do exactly what you've been doing, and perform a TO_CHAR conversion prior to input to the operator. JSON doesn't have a native temporal type so it's ultimately rendered as a string either way.

              • 4. Re: Date Formats when using JSON_OBJECT sql function
                DavidGaskell

                Thanks both for the responses.

                 

                The primary issue I have is this is a legacy database so all date/time fields are stored as DATE types. Hence the suggestion to cast to timestamp is the only viable solution. I'm still seeing some behaviour within 12.2 which I'm currently unable to verify if this is as expected or a bug. I don't have an 18c DB to validate against, however upgrading to 18c isn't currently a viable proposition anyway if this behaviour is different there.

                 

                Using the following SQL in SQL PLUS:

                 

                select sessiontimezone,
                      sysdate,
                      cast(sysdate as timestamp with time zone) at time zone 'UTC' sysdate_in_UTC,
                      json_object('systimestamp' value systimestamp) as systimestamp,
                      json_object('caseToTS' value cast(sysdate as TIMESTAMP) ) as caseToTS,
                      json_object('caseToTSWithtimezone' value cast(sysdate as TIMESTAMP WITH TIME ZONE) ) as caseToTSWithtimezone,
                      json_object('caseToTSWithtimezoneAtUTC' value cast(sysdate as TIMESTAMP WITH TIME ZONE) at time zone 'UTC' ) as caseToTSWithtimezoneatUTC
                from dual; 
                

                 

                I get the following results

                SESSIONTIMEZONE
                ---------------------------------------------------------------------------
                SYSDATE
                ---------------------------------------------------------------------------
                SYSDATE_IN_UTC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                --------------------------------------------------------------------------- 
                SYSTIMESTAMP
                ---------------------------------------------------------------------------
                CASETOTS
                ---------------------------------------------------------------------------
                CASETOTSWITHTIMEZONE
                ---------------------------------------------------------------------------
                CASETOTSWITHTIMEZONEUTC
                ---------------------------------------------------------------------------
                +00:00             
                25-JUN-19 
                25-JUN-19 11.47.59.000000 AM UTC
                {"systimestamp":"2019-06-25T10:47:59.346501Z"}
                {"caseToTS":"2019-06-25T11:47:59"}    
                {"caseToTSWithtimezone":"2019-06-25T11:47:59"}
                {"caseToTSWithtimezoneAtUTC":}
                

                 

                As can be seen from above, the UK is in BST. Trying to cast to a timezone of UTC results in null when using the JSON Function. However using in plain old SQL shows a format.

                 

                I'm happy that this is most likely down to support of the functions in 12.2

                 

                Kind Regards