Skip to Main Content
  • Questions
  • Timestamp of oracle and ORA-30088: datetime/interval precision is out of range

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, bhogeswara.

Asked: March 01, 2018 - 5:05 pm UTC

Last updated: March 02, 2018 - 11:20 am UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

Hi Tom

I am trying to store timestamp as below.

Timstmp(12)
YYYY-MM-DD-hh.mm.ss.nnnnnnnnnnnn

So i am trying to create the table as below.

create table t23
    (ts0 timestamp(0)
     , ts3 timestamp(3)
    , ts6 timestamp(6)
    ,  ts12 timestamp(12)
);

ORA-30088: datetime/interval precision is out of range
30088. 00000 -  "datetime/interval precision is out of range"
*Cause:    The specified datetime/interval precision was not between 0 and 9.
*Action:   Use a value between 0 and 9 for datetime/interval precision.


Becuase of ts12 timestamp(12) is giving the error. Please help on this.

and Chris said...

The error seems clear to me here:

The specified datetime/interval precision was not between 0 and 9

So you can't have a timestamp(12). Timestamp(9) is the maximum precision you can have.

Rating

  (1 rating)

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

Comments

BHOGESWARA, March 01, 2018 - 6:23 pm UTC

Hi Tom
If I want to store the date and time as below

YYYY-MM-DD-hh.mm.ss.nnnnnnnnnnnn

what datatype can I use.
Chris Saxon
March 02, 2018 - 11:20 am UTC

Are really capturing measurements with a precision of picoseconds?

Oracle Database doesn't support that level of precision with timestamps.

If you must support picoseconds you'll have to store it as a varchar2.


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.