Was this helpful?
Date and Time Data Types
Date and time data types include the following:
Date data types
Time data types
Timestamp data types
Interval data types
Date and Time Input Formats
Datetime data values have the following data type input formats:
Ansidate
Ingresdate
Time (without time zone)
Time with time zone
Time with local time zone
Timestamp (without time zone)
Timestamp with time zone
Timestamp with local time zone
Interval year to month
Interval day to second
Date Data Type
The declaration format of the date type can be one of the following:
DATE
ANSIDATE
INGRESDATE
Examples:
Date Format
Example
DATE
The keyword DATE used for a column data type is an alias, which can be configured to either ANSIDATE or INGRESDATE by setting the configuration parameter date_alias.
ANSIDATE
2006-05-16
INGRESDATE
For more information, see Ingresdate Data Types.
Time Data Types
The TIME data type consists of a time in hour, minutes, seconds, optional fractions of a second, and optional time zone.
The format is as follows:
TIME [time_precision] [time_zone_spec]
time_precision
(Optional) Indicates the number of digits of precision in the fractions of seconds, as an integer value from 0 to 9. When no time precision is supplied, the value of time_precision is set to 0 by default.
Note:  When a value is entered that exceeds the specified precision, the value is truncated at the specified precision.
time_zone_spec
(Optional) Specifies a time zone as one of the following:
WITH TIME ZONE
WITHOUT TIME ZONE
WITH LOCAL TIME ZONE
Note:  The time value in TIME WITH TIME ZONE data type indicates the time at the specified time zone.
Examples:
Time Format
Example
TIME(5) WITH TIME ZONE
12:30:55.12345-05:00
TIME(4) WITHOUT TIME ZONE
12:30:55.1234
TIME(9) WITH LOCAL TIME ZONE
12:30:55.123456789
Timestamp Data Types
The TIMESTAMP data type consists of a date and time, with optional time zone.
The format is as follows:
TIMESTAMP [(timestamp_precision)] [time_zone_spec]
timestamp_precision
(Optional) Indicates the number of digits of precision in the fractions of seconds, as an integer value from 0 to 9. The default is 6.
Note:  When a value is entered that exceeds the specified precision, the value is truncated at the specified precision.
time_zone_spec
(Optional) Specifies a time zone as one of the following:
WITH TIME ZONE
WITHOUT TIME ZONE
WITH LOCAL TIME ZONE
Note:  The time value in TIMESTAMP WITH TIME ZONE data type indicates the time at the specified time zone.
Examples:
Timestamp Format
Example
TIMESTAMP
2006-12-15 12:30:55.123456
TIMESTAMP(4) WITHOUT TIME ZONE
2006-12-15 12:30:55.1234
TIMESTAMP(5) WITH TIME ZONE
2006-12-15 9:30:55.12345-08:00
TIMESTAMP(9) WITH LOCAL TIME ZONE
2006-12-15 12:30:55.123456789
Note:  Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the database. For example:
TIMESTAMP '2006-12-15 9:30:55 -8:00'
is the same as
TIMESTAMP '2006-12-15 12:30:55 -5:00'
Note:  The ANSI datetimes can also be loaded using the II_DATE_FORMAT for Absolute Ingresdate (see II_DATE_FORMAT for Absolute Ingresdate) input formats.
Interval Data Types
The INTERVAL data types include year to month and day to second intervals.
The format is as follows:
INTERVAL interval_qualifier
interval_qualifier
Defines an interval column as one of the following:
YEAR TO MONTH
DAY TO SECOND [(second_precision)]
where second_precision is the number of digits in the fractions of seconds field.
Note:  When a value is entered that exceeds the specified precision, the value is truncated at the specified precision.
Examples:
Interval Format
Example
Explanation
INTERVAL YEAR TO MONTH
123-04
An interval of 123 years, 4 months
INTERVAL DAY TO SECOND(3)
7 6:54:32.123
An interval of 7 days, 6 hours, 54 minutes, 32 seconds and 123 thousandths of a second
Note:  The ANSI intervals can also be loaded using the Interval Input for Ingresdate (see Interval Input for Ingresdate) format.
Summary of ANSI Datetime Data Types
The following table summarizes valid input and output formats for ANSI datetime data types:
Data Type
Format
Example
ANSIDATE
yyyy-mm-dd
2007-02-28
TIME WITH TIME ZONE
hh:mm:ss.ffff... [+|-]th:tm
12:45:12.23456 -05:00
TIME or TIME WITHOUT TIME ZONE
hh:mm:ss.ffff...
12:45:12.23456
TIME WITH LOCAL TIME ZONE
hh:mm:ss.ffff...
12:45:12.23456
TIMESTAMP WITH TIME ZONE
yyyy-mm-dd hh:nn:ss.ffff... [+|-]th:tm
2007-02-28 12:45:12.23456 -05:00
TIMESTAMP or TIMESTAMP WITHOUT TIME ZONE
yyyy-mm-dd hh:nn:ss.ffff...
2007-02-28 12:45:12.23456
TIMESTAMP WITH LOCAL TIME ZONE
yyyy-mm-dd hh:nn:ss.ffff...
2007-02-28 12:45:12.23456
INTERVAL YEAR TO MONTH
[+|-]years-mm
55-4
INTERVAL DAY TO SECOND
[+|-]days hh:nn:ss.ffff...
-18 12:02:23.12345
where:
yyyy
Is a four-digit year value. All four digits are required.
mm
Is a two-digit month value between 01 to 12.
dd
Is a two-digit day value between 01 to 31.
hh
Is a two-digit hour value between 00 to 23.
nn
Is a two-digit minute value between 00 to 59.
th
Is a two-digit hour value between -12 to +14.
tm
Is a two-digit minute value between 00 to 59.
years
Is the number of years. Maximum of 9999.
days
Is the number of days. Maximum of 3652047.
ss
Is a two-digit seconds value between 0 and 59.
ffff...
Is 1 to 9 digits fractions of a second.
Ingresdate Data Types
The ingresdate data type is an abstract data type. The ingresdate data type input formats are as follows:
Absolute date
Absolute time
Combined date and time
Time interval
Ingresdate Input
Ingresdate values are specified as quoted character strings. A date can be entered by itself or with a time value. If a date is entered without the time, no time is shown when the data displays (see How Ingres Dates and Times Are Displayed).
Because ingresdate can store different forms of date, time, and interval information, the nature of the value is determined by the input format.
Ingresdate absolute formats recognized are determined by the active date format, which can be altered to suit particular country preferences.
Ingresdate interval forms follow a simple form that is distinct from the absolute forms.
II_DATE_FORMAT for Absolute Ingresdate
The II_DATE_FORMAT setting determines the legal formats for absolute ingresdate values. The default setting is US.
II_DATE_FORMAT can be set on a session basis using the SET DATE_FORMAT statement.
The following table lists ingresdate input and output formats:
Setting
Valid Input Formats
Output Format
US (default)
mm/dd/yy
mm-dd-yy
mmddyy
mm/dd/yyyy
mm-dd-yyyy
mmddyyyy
dd-mmm-yyyy
dd mmm yyyy
yyyy-mm-dd
yyyy.mm.dd
yyyy_mm_dd
mm-dd
mm/dd
dd-mmm-yyyy
MULTINATIONAL
yyyy-mm-dd
mm-dd-yy
mmddyy
mmddyyyy
dd/mm/yy
dd/mm/yyyy
dd mmm yyyy
All US formats except mm/dd/yyyy and
mm/dd/yy
dd/mm/yy
MULTINATIONAL4
yyyy-mm-dd
dd/mm/yy
dd/mm/yyyy
dd mmm yyyy
mm-dd-yy
mmddyy
mmddyyyy
All US formats except mm/dd/yyyy and
mm/dd/yy
dd/mm/yyyy
ISO
yyyy-mm-dd
yyyymmdd
yymmdd
ymmdd
mmdd
mdd
dd mmm yyyy
All US input formats except mmddyy and
mmddyyyy
yymmdd
ISO8601
All ISO input formats
yyyy-dd-mmThh:mm:ssZ
where hh is in 24-hour format
and Z indicates Zulu (UTC)
timezone
With offsets:
yyyy-dd-mmThh:mm:ss[+|-]hh:mm
yyyy-dd-mmThh:mm:ss[+|-]hhmm
yyyy-dd-mmThh:mm:ss[+|-]hh
ISO4
yyyy-mm-dd
yyyymmdd
yymmdd
ymmdd
mmdd
mdd
dd mmm yyyy
All US input formats except mmddyy and
mmddyyyy
yyyymmdd
ISO4T
All ISO4 input formats.
If the prefix “T” is used then the absolute
time component can use the input format
hhmmss as well as the standard hh:mm:ss.
For example:
Acceptable: yyyymmddThhmmss,
yyyymmddThh:mm:ss, and yyyymmdd
hh:mm:ss
Not acceptable:
yyyymmdd hhmmss
Acceptable:
'Thhmmss', 'Thh:mm:ss' and 'hh:mm:ss'
Not acceptable:
Note:  'hhmmss'This is the only case where an absolute time can be entered in format hhmmss instead of hh:mm:ss. To avoid ambiguity, a time field entered in hhmmss format must be 6 characters long.
yyyymmdd
ISO4 output format, unless the date includes a time, in which case the format is: yyyymmddThhmmss
ISO4TC
See description under ISO4T.
ISO4 output format, unless the date includes a time, in which case the format is:
yyyymmddThh:mm:ss
SWEDEN or FINLAND
yyyy-mm-dd
yy-mm-dd
mmddyy
dd mmm yyyy
All US input formats
except mm-dd-yy
yyyy-mm-dd
GERMAN
yyyy-mm-dd
dd.mm.yyyy
ddmmyy
dmmyy
dmmyyyy
ddmmyyyy
dd mmm yyyy
mm-dd-yy
All US input formats except yyyy.mm.dd
and mmddyy
dd.mm.yyyy
YMD
mm/dd
mm-dd
mmdd
yymdd
yymmdd
yy-mm-dd
yyyymdd
yyyy-mmm-dd
yyyy/mm/dd
yyyy.mm.dd
yyyy-mm-dd
yyyy_mm_dd
yyyymmdd
yyyy mmm dd
yyyy-mmm-dd
DMY
yyyy-mm-dd
yyyy_mm_dd
dd/mm
dd-mm
ddmm
ddmyy
dd-mm-yy
ddmmyy
ddmyyyy
ddmmyyyy
dd/mm/yyyy
dd-mm-yyyy
dd.mm.yyyy
dd-mmm-yyyy
dd mmm yyyy
dd-mmm-yyyy
MDY
yyyy-mm-dd
yyyy_mm_dd
mm/dd
mm-dd
mmdd
mmddyy
mddyy
mddyyyy
mm-dd-yy
mm-dd-yyyy
mm/dd/yyyy
mm.dd.yyyy
mmddyyyy
mmm-dd-yyyy
mmm-dd-yyyy
For example, if the II_DATE_FORMAT setting is ISO, the dates are output as yymmdd. If you
enter a date using a similar, six-character format, such as mmddyy, the date is interpreted on
output as yymmdd. For example, if you enter the date March 9, 1912, as 030912 (mmddyy) and
II_DATE_FORMAT is set to ISO, this date is interpreted as Sept. 12, 1903 (030912 as yymmdd).
If the date you entered cannot be interpreted as a valid date in the output format, you receive an
error message.
Year defaults to the current year. In formats that include delimiters (such as forward slashes or
dashes), specify the last two digits of the year; the first two digits default to the current century
(2000). For example, if this date is entered:
'03/21/03'
using the format mm/dd/yyyy, the DBMS Server assumes that you are referring to March 21,
2003.
In three-character month formats, for example, dd-mmm-yy, specify three-letter abbreviations for
the month (for example, mar, apr, may).
To specify the current system date, use the constant, TODAY. For example:
SELECT DATE('TODAY');
To specify the current system date and time, use the constant, now.
For convenience, the ANSI datetime formats are also accepted as input to ingresdates.
Note:  When using the _date4() function (MULTINATIONAL4), the output format for the year value always returns ‘yyyy’.
II_DATE_CENTURY_BOUNDARY for Absolute Ingresdate
The II_DATE_CENTURY_BOUNDARY variable, which can be set to an integer in the 0< n <=100 range, dictates the implied century for an ingresdate value when only the last two digits of the year are entered.
For example, if II_DATE_CENTURY_BOUNDARY is 50 and the current year is 1999, an input date of 3/17/51 is treated as March 17, 1951, but a date of 03/17/49 is treated as March 17, 2049.
If the II_DATE_CENTURY_BOUNDARY variable is not set or if it is set to 0 or 100, the current century is used. If the user enters the full four digits for the year in a four-digit year field in the application, the year is accepted as entered, regardless of the II_DATE_CENTURY_BOUNDARY setting.
Absolute Time Input for Ingresdate
The format for inputting an absolute time into an ingresdate value is:
'hh:mm[:ss] [am|pm] [timezone]'
Input formats for absolute times are assumed to be on a 24-hour clock. If a time with the designation am or pm is entered, the time is converted to a 24-hour internal and displayed representation.
If timezone is omitted, the local time zone designation is assumed. Times are stored as Greenwich Mean Time (GMT) and displayed using the time zone adjustment specified by II_TIMEZONE_NAME.
If an absolute time without a date is entered, the date defaults to the current system date.
Combined Date and Time Input for Ingresdate
Any valid absolute date input format can be paired with a valid absolute time input format to form a valid date and time entry in an ingresdate. The following table shows examples of valid date and time entries, using the US absolute date input formats:
Format
Example
mm/dd/yy hh:mm:ss
11/15/03 10:30:00
dd-mmm-yy hh:mm:ss
15-nov-03 10:30:00
mm/dd/yy hh:mm:ss
11/15/03 10:30:00
dd-mmm-yy hh:mm:ss gmt
15-nov-03 10:30:00 gmt
dd-mmm-yy hh:mm:ss [am|pm]
15-nov-03 10:30:00 am
mm/dd/yy hh:mm
11/15/03 10:30
dd-mmm-yy hh:mm
15-nov-03 10:30
mm/dd/yy hh:mm
11/15/03 10:30
dd-mmm-yy hh:mm
15-nov-03 10:30
Interval Input for Ingresdate
Ingres interval data includes date intervals, time intervals, or a combination.
An ingresdate interval value is entered as a quoted string of qualified numbers that mark the units of the interval. For example 18 months might be represented as:
'1 year 6 months'
The interval syntax is of the form:
'[ n YEARS][ n MONTHS][ n DAYS][ n HOURS][ n MINUTES][n SECONDS]'
where n can be a positive or negative integer. The interval qualifiers can be abbreviated:
Interval
Abbreviation
YEARS
YEAR, YRS, YR
QUARTERS
QUARTERS, QTRS, QTR
MONTHS
MONTH, MOS, MO
WEEKS
WEEK, WKS, WK
DAYS
DAY
HOURS
HOURS, HRS, HR
MINUTES
MINUTE, MINS, MIN
SECONDS
SECOND, SECS, SEC
Here are example date intervals:
'5 years'
'8 months'
'14 days'
'5 yrs 8 mos 14 days'
'5 years 8 months'
'5 years 14 days'
'8 months 14 days'
Here are example time intervals:
'23 hours'
'38 minutes'
'53 seconds'
'23 hrs 38 mins 53 secs'
'23 hrs 53 seconds'
'28 hrs 38 mins'
'38 mins 53 secs'
'23:38 hours'
'23:38:53 hours'
If a time interval greater than 1 day is entered, the interval is converted to a date and time interval. For example:
'26 hours'
is converted to:
'1 day 2 hours'
Valid ranges for Ingres date and time intervals are:
Interval
Range
YEARS
-9999 to +9999
MONTHS
-119988 to +119988
DAYS
-3652047 to +3652047
HOURS, DAYS, SECONDS
-2,147,483,639 to +2,147,483,639
For convenience, the ANSI interval input formats can also be used for loading ingresdate intervals.
How Ingres Dates and Times Are Displayed
Absolute Dates
By default ingresdate absolute values (date, time, or timestamp) are displayed as strings of left-justified 25 characters with trailing blanks.
The display format for an absolute ingresdate is determined by the II_DATE_FORMAT (see II_DATE_FORMAT for Absolute Ingresdate) or the SQL statement SET DATE_FORMAT (see DATE_FORMAT).
The display format for an absolute time is:
hh:mm:ss
The DBMS Server displays 24-hour times for the current time zone. Times are stored as Greenwich Mean Time (GMT) and displayed using the time zone adjustment specified by II_TIMEZONE_NAME.
If a date is not entered when entering a time, the current date is inserted in the date place. If seconds are not entered when entering a time, zeros display in the seconds place.
Intervals
By default ingresdate interval is displayed as the first 25 characters of the interval string; the remainder of the interval will be truncated. The maximum length of an ingresdate interval is 57 characters.
The complete format appears as:
[-]yy yrs [-]mm mos [-]dd days [-]hh hrs [-]mm mins [-]ss secs
What is stored in the ingresdate interval determines what is displayed in the 25 characters. By default the "least significant" portion is truncated to fit the result into the 25 characters. What is deemed as "least significant" is a function of the size of any component of the time interval. Trailing blanks are appended to fill out the string to 25 characters.
For example, if the following ingresdate interval had been inserted into an ingresdate column:
5 yrs 4 mos 3 days 12 hrs 32 min 14 secs
when selected within terminal monitor, by default, the following would be returned:
5 yrs 4 mos 3 days 12 hrs
The least significant portion of the interval, minutes and seconds, have been lost due to truncation to fit the result into 25 characters. This happens silently—there are no warnings or error messages.
To display the full string, it is necessary to cast the column to a character string of the required length. For example:
CHAR(my_interval,60)
returns the full value plus trailing spaces to the length specified in the cast:
5 yrs 4 mos 3 days 12 hrs 32 min 14 secs
Coercion Between Datetime Data Types
The rules governing coercion between the various datetime data types are as follows:
1. Ansidate cannot be converted to any of the time types, nor can the time types be converted to ansidate. Doing so results in error E_AD5066_DATE_COERCION.
2. When converting from a data type that does not have time zone information to a data type with time zone value (for example, ansidate to a timestamp with time zone, time without time zone to a time with time zone), the time zone is set to the current session time zone.
Example: In Eastern Standard Time (EST) time zone (that is, -05:00), the following statements insert a value of 2007-02-08 16:41:00-05:00 in the database.
CREATE TABLE tab (col1 TIMESTAMP WITH TIME ZONE);
INSERT INTO tab VALUES (TIMESTAMP ‘2007-02-08 16:41:00’);
3. Ingresdate, time with local time zone, and timestamp with local time zone, store datetime values in UTC. When converting from other data types like ansidate, time with/without time zone, timestamp with/without time zone to these data types, the session time zone displacement is subtracted from the datetime value. On the reverse operation, when converting from ansidate, time with/without time zone, timestamp with/without time zone to ingresdate, time with local time zone and timestamp with local time zone, the session time zone displacement is added to the datetime value in the database to make it in local time zone.
Example: In EST time zone (with time zone displacement of -05 :00), the following query stores a value of 2007-02-18 15:04:12 in the database:
CREATE TABLE tab (col1 TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO tab VALUES (TIMESTAMP ‘2007-02-18 10:04:12’);
If this value was selected in PST time zone (with time zone displacement of -08:00), the session time zone value is added to the value stored in database and the value in local time zone is displayed, that is:
2007-02-18 07:04:12
4. When a time value is converted to a timestamp, date, or time/timestamp with local time zone types, the year, month, and day fields are filled with the current year, month, and day value.
Example: If current date is 08 Feb 2007 then the following statements insert a value of 2007-02-08 17:01:00 in the database:
CREATE TABLE tab (col1 TIMESTAMP);
INSERT INTO tab VALUES (TIME ’17:01:00’);
5. When converting from time without time zone to time with local time zone, the following procedure is used:
a. Current date is added to the time value to make a timestamp.
b. Time zone displacement is then applied to the time value.
c. The date part is removed from the result.
Example: If current date is 08 Feb 2007 and the session time zone is -05 :00 (EST), the following query stores a value of 22:01:00 in the database:
CREATE TABLE tab (col1 TIME WITH LOCAL TIME ZONE);
INSERT INTO tab VALUES (TIME ’17:01:00’);
6. INTERVAL types cannot be converted to any other types except themselves and ingresdates.
7. When a time/timestamp with time zone is converted to ingresdate, time with local time zone, or timestamp with local time zone, the time value is converted to UTC by applying the time zone information in the value.
Example: In any time zone, the following query will insert a value of 2007-02-18 03:04:12 in the database:
CREATE TABLE tab (col1 TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO tab VALUES (TIMESTAMP ‘2007-02-18 10:04:12-07:00’);
Last modified date: 08/29/2024