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 laterInformation 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 |