My Oracle Support Banner

Function JSON_OBJECT Returns No Zeros As Fraction, If A Timestamp Value Is Exactly At Seconds Bound (Doc ID 2958475.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 19.19.0.0.0 and later
Information in this document applies to any platform.

Symptoms

The function JSON_OBJECT does not return a JSON value with the number of fraction digits according to the timestamp data type if the timestamp value happens to be on the seconds boundary.
---------


Simple Test case:


--JSON_OBJECT when not at full second boundary
SELECT JSON_OBJECT(Key 'ST' VALUE TO_TIMESTAMP('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')) FROM DUAL;

JSON_OBJECT(KEY'ST'VALUETO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:M
--------------------------------------------------------------------------------
{"ST":"2002-09-10T14:10:10.123000"}


--JSON_OBJECT at full second boundary:

SELECT JSON_OBJECT(Key 'ST' VALUE TO_TIMESTAMP('10-Sep-02 14:10:10.000000', 'DD-Mon-RR HH24:MI:SS.FF')) FROM DUAL;

JSON_OBJECT(KEY'ST'VALUETO_TIMESTAMP('10-SEP-0214:10:10.000000','DD-MON-RRHH24:M
--------------------------------------------------------------------------------
{"ST":"2002-09-10T14:10:10"} 

 

--expected result:

JSON_OBJECT(KEY'ST'VALUETO_TIMESTAMP('10-SEP-0214:10:10.000000','DD-MON-RRHH24:M
--------------------------------------------------------------------------------
{"ST":"2002-09-10T14:10:10:000000"}  


--without the use of  JSON_OBJECT, when not at full second boundary
select TO_TIMESTAMP('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-SEP-02 02.10.10.123000000 PM


---without the use of  JSON_OBJECT, when at full second boundary
select TO_TIMESTAMP('10-Sep-02 14:10:10.000000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

TO_TIMESTAMP('10-SEP-0214:10:10.000000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-SEP-02 02.10.10.000000000 PM   

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.