TIME ZONE DATA TYPES

This is important in an increasingly globalized economy where businesses need to maintain time zone management of data

Datetime and Interval Data Types

The datetime datatypes are

  • DATE Data Type
  • TIMESTAMP Data Type
  • TIMESTAMP WITH TIME ZONE Data Type
  • TIMESTAMP WITH LOCAL TIME ZONE Data Type

The interval data types are

  • INTERVAL YEAR TO MONTH 
  • INTERVAL DAY TO SECOND

DATE Data Type

The DATE data type stores date and time information

Date can be specified as an ANSI date type or an Oracle Database date value

The ANSI date literal does not contain any time

ANSI: DATE ‘YYYY-MM-DD’

example: DATE ‘1998-12-25’

Alternatively, you can specify an Oracle Database date value as shown in the following example:

TO_DATE(‘1998-DEC-25 17:30′,’YYYY-MON-DD HH24:MI’,’NLS_DATE_LANGUAGE=AMERICAN’)

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

TIMESTAMP Data Type

  • The TIMESTAMP data type is an extension of the DATE data type.
  • It stores year, month, day, hour, minute, and second values.
  • It also stores fractional seconds, which are not stored by the DATE data type.

SYNTAX FOR TIMESTAMP DATA TYPE

  • fractional_seconds_precision is optional
  • specifies the number of digits in the fractional part of the SECOND datetime field. It can be a number in the range 0 to 9. The default is 6.

For example, '26-JUN-02 09:39:16.78' shows 16.78 seconds. The fractional seconds precision is 2 because there are 2 digits in ‘78‘.

You can specify the TIMESTAMP literal in a format like the following:

TIMESTAMP ‘YYYY-MM-DD HH24:MI:SS.FF’

Using the example format, specify TIMESTAMP as a literal as follows:

TIMESTAMP ‘1997-01-31 09:26:50.12’

The value of NLS_TIMESTAMP_FORMAT initialization parameter determines the timestamp format when a character string is converted to the TIMESTAMP data type. NLS_DATE_LANGUAGE determines the language used for character data such as MON.

TIMESTAMP WITH TIME ZONE Data Type

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time). Specify the TIMESTAMP WITH TIME ZONE data type as follows:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field.

You can specify TIMESTAMP WITH TIME ZONE as a literal as follows:

TIMESTAMP ‘1997-01-31 09:26:56.66 +02:00’

TIMESTAMP ‘1999-01-15 8:00:00 -8:00’
TIMESTAMP ‘1999-01-15 11:00:00 -5:00’

TIMESTAMP ‘1999-01-15 8:00:00 America/Los_Angeles’

TIMESTAMP ‘1999-10-29 01:30:00 America/Los_Angeles PDT’

TIMESTAMP ‘1999-10-29 01:30:00 America/Los_Angeles PDT’

the PDT at the end specifies daylight saving time

Interval Data Type

INTERVAL YEAR TO MONTH Data Type

The INTERNVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. This data type si useful for representing the difference between two datetime values when only the year and monthe values are significant.;

Specify INTERVAL YEAR TO MONTH as follows:

where year_precision is the number of digits in the YEAR datetime field. The default value of year_precision is 2.

INTERVAL DAY TO SECOND Data Type

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

Specify this data type as follows:

where

  • day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
  • fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.

EXAMPLE

https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/datetime-data-types-and-time-zone-support.html

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top