Skip to Main Content
  • Questions

  • Assistance required to activate nanoseconds in timestamp datatype

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manoj.

Asked: July 21, 2022 - 1:41 pm UTC

Last updated: July 22, 2022 - 5:04 pm UTC

Version: 19.2.1

Viewed 1000+ times

You Asked

We are using localtimestamp in our jobs to store that value we are using timestamp(9) datatype however while storing value into table we are getting precision up to 6 digits in fraction seconds other three digits are filled with 0. But we need nanoseconds to be stored as well in the table that is 9 digit precision so anyone please help me to get this. PFB the output of the localtimestamp we are using.

select localtimestamp(9) from dual;


output:
21-JUL-22 06.32.21.968835000 AM

21-JUL-22 06.32.48.898691000 AM


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/nswexkn6e210sn30kepvyj3vj

and Chris said...

The value for LOCALTIMESTAMP is derived from the database server's operating system. It's this that determines the precision. Most Unix-based platforms only report microseconds, so there's no way to using the *TIMESTAMP functions to get nanoseconds.

If you truly need nanosecond precision (why?), you'll have to find a different source that has this granularity.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Manoj Kapatakar, July 22, 2022 - 2:32 pm UTC

Could you please provide us the datatype name(source) which we can use to strore the value same as timestamp ones with nano seconds part also.
Chris Saxon
July 22, 2022 - 5:04 pm UTC

If LOCALTIMESTAMP gives you microseconds, that's all that's available in the database. You'll have to find something else.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library