Oracle Timestamp, Max and Minimal Values

Question

I was searching, also in the Oracle Doc, for the following:

• What is the range for Timestamp in Oracle?

I know for date it is -4712, Jan-01 to 9999 Dec-31, but what for Timestamp?

Anyone a clue or hint where I can search?

Answer 1

It would be surprising if the range for the DATE portion of a TIMESTAMP was smaller than the range for a DATE, so it should be:

-4712-01-01 00:00:00  to 9999-12-31 23:59:59.999999

That assumes no time zone; the UTC value is probably constrained to that range, but someone in an Eastern time zone might manage to see a data value on 1000-01-01 in their time zone.

It is hard to find definitive data off Oracle's site. The best I found in a casual survey was:

• http://www.techonthenet.com/oracle/datatypes.php

There are probably others.

---

I found a quote which says:

TIMESTAMP Datatype

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values.

Answer 2

You can always just try it:

SQL> select to_timestamp( '9999-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' ) from dual;

TO_TIMESTAMP('9999-12-3123:59:59','YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------------------------
31-DEC-99 11.59.59.000000000 PM

and:

SQL> select to_timestamp( '9999-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' )+1 from dual;
select to_timestamp( '9999-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss' )+1 from dual
                                                                     *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0