OceanBase Version V4.2
I have consulted the OceanBase official documentation. The precision of the function CURRENT_TIMESTAMP() is 9 digits, which is enough to get nanosecond time. However, in my environment, the precision is only 6 digits, and the last 3 digits are padded with zeros.
SELECT
TO_TIMESTAMP(‘2003-10-24 10:48:45.656123456’,‘YYYY-MM-DD HH24:MI:SS:FF9’) TO_TS9,
TO_CHAR(TO_TIMESTAMP(‘2003-10-24 10:48:45.656123456’,‘YYYY-MM-DD HH24:MI:SSXFF’),‘YYYY-MM-DD HH24:MI:SS:FF9’) TO_CS9,
TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS.FF9’) AS TO_CS92,
CURRENT_TIMESTAMP(9) TIMESTAMP9
FROM DUAL;
*************************** 1. row ***************************
TO_TS9: 2003-10-24 10:48:45.656123456
TO_CS9: 2003-10-24 10:48:45:656123456
TO_CS92: 2025-03-14 10:24:38.757049000
TIMESTAMP9: 14-MAR-25 10.24.38.757054000 AM +08:00
From the output of the above SQL, it can display 9-digit nanosecond data. How can I obtain the nanosecond time in the database?
I want to get the time with 9-digit precision, that is, nanosecond time, but I can only get the microsecond time with 6-digit precision, and the last 3 digits are padded with zeros. How can I get the nanosecond time in the database?
In OceanBase MySQL mode, the NOW() and CURRENT_TIMESTAMP functions return time with microsecond precision (up to 6 decimal places).
If your OceanBase instance is running in Oracle mode, functions like SYSTIMESTAMP and CURRENT_TIMESTAMP can return time with nanosecond precision (up to 9 decimal places).
If you need nanosecond-level timestamps in MySQL mode, you can generate them using external applications — for example, with System.nanoTime() in Java or time.Now().UnixNano() in Go — and store them in the database as BIGINT or a custom-formatted string.