Datetime and Interval Datatypes Fields and Values in Oracle

•In oracle, the datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

•Both Datetime and Interval datatypes in oracle are made up of fields. These fields determines the value of these datatypes.

•Database and sesion time zone can be verified by querying the built-in SQL functions DBTIMEZONE and SESSIONTIMEZONE.

•If the time zones have not been set manually, Oracle Database uses the OS time zone by default. If the OS time zone is not a valid Oracle time zone, then Oracle uses UTC as the default value.

•From now on datetime datatypes will be referred as datatimes and interval datatypes will be referred as intervals.

Datetime Fields and Values
---------------------------------------
YEAR: The valid value of YEAR field are from -4712 to 9999 (excluding year 0) in case of datetimes and in case of intervals any integer values are valid.

MONTH:The valid value of MONTH field are from 01 to 12 in case of datetimes and from 0 to 11 in case of intervals.

DAY:The valid value of DAY field are from 0 to 31 in case of datetimes and in case of intervals any integer values are valid.

HOUR:Valid value ranges from 00 to 23.

MINUTE:Valid value ranges from 00 to 59.

SECOND:Valid value ranges from 00 to 59.9(n). It is not applicable for DATE datatype.

TIMEZONE_HOUR:Only applicable for datetimes (except DATE and TIMESTAMP) and valid value ranges from -12 to 14.

TIMEZONE_MINUTE:Only applicable for datetimes (except DATE and TIMESTAMP) and valid value ranges from 0 to 9.

TIMEZONE_REGION:Only applicable for datetimes except DATE and TIMESTAMP) and to know the valid values query from V$TIMEZONE_NAME,
SQL> select distinct TZNAME from V$TIMEZONE_NAMES;

TIMEZONE_ABBR:Only applicable for datetimes except DATE and TIMESTAMP) and to know the valid values query from V$TIMEZONE_NAME,
SQL> select distinct TZABBREV from V$TIMEZONE_NAMES;
DateTime DataTypes
--------------------------------------
1)DATE Datatype
2)TIMESTAMP Datatype
3)TIMESTAMP WITH TIME ZONE Datatype
4)TIMESTAMP WITH LOCAL TIME ZONE Datatype

Interval DataTypes
-----------------------------------------
1)INTERVAL YEAR TO MONTH Datatype
2)INTERVAL DAY TO SECOND Datatype