HistoryContents
A temporal <data type> is either a datetime <data type> or an interval <data type>.
Datetime <data type>s
A datetime <data type> is defined by a descriptor that contains two pieces of information:
- The <data type>'s name: either
DATE,TIME,TIME WITH TIME ZONE,TIMESTAMPorTIMESTAMP WITH TIME ZONE. - The <data type>s fractional seconds precision (for
TIME,TIME WITH TIME ZONE,TIMESTAMPandTIMESTAMP WITH TIME ZONEtypes).
DATE
The required syntax for a DATE <data type> specification is:
DATE ::= DATE
DATE combines the datetime fields YEAR,
MONTH and DAY; it defines a set of
correctly formed values that represent any valid Gregorian calendar date
between '0001-01-01' and '9999-12-31'
(i.e.: between January 1, 1 AD and December 31, 9999 AD). It has a length of 10
positions.
DATE expects dates to have the following form:
yyyy-mm-dd
e.g.: this date represents "July 15, 1994":
1994-07-15
Any operation that attempts to make a DATE <data type>
contain a YEAR value that is either less than 1 or greater
than 9999 will fail: the DBMS will return the
SQLSTATE error 22007 "data exception-invalid datetime format".
Here is an example of DATE:
CREATE TABLE date_table_1 ( start_date DATE); INSERT INTO date_table_1 (start_date) VALUES (DATE '1996-01-01');
TIME
The required syntax for a TIME <data type> specification is:
TIME ::= TIME [ (fractional seconds precision) ] [ WITHOUT TIME ZONE ]
TIME (or TIME WITHOUT TIME ZONE)
combines the datetime fields HOUR,
MINUTE and SECOND; it defines a set of
correctly formed values that represent any valid time of day (based on a 24
hour clock) between '00:00:00' and (at a minimum)
'23:59:61.999999'. (The SQL Standard requires DBMSs to
allow for the addition of up to 2 "leap" seconds in a valid time.) It has a
length of at least 8 positions.
The optional fractional seconds precision, if specified, is an unsigned integer
that specifies the number of digits following the decimal point in the
SECOND datetime field. The minimum fractional seconds
precision and the default fractional seconds precision are both zero. For
example, these two <data type> specifications both define a set of times with a
fractional seconds precision of zero digits:
TIME -- would contain values like 13:30:22 TIME(0) -- would also contain values like 13:30:22
This <data type> specification defines a set of times with a fractional seconds precision of two digits, i.e.: of one-hundredth of a second:
TIME(2) -- would contain values like 13:30:22.05
[NON-PORTABLE] The maximum fractional seconds precision for
TIME (a) may not be less than 6 digits and (b) must
be equal to the maximum allowed for the
TIME WITH TIME ZONE, TIMESTAMP and
TIMESTAMP WITH TIME ZONE <data type>s but is non-standard
because the SQL Standard requires implementors to define
TIME's maximum fractional seconds precision.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
the fractional seconds precision of TIME to range from
0 to 6 digits.
TIME expects times to have the following form:
hh:mm:ss[.nnnnnn]
e.g.: these two times both represent "half past one, plus 22 seconds, PM":
13:30:22 13:30:22.00
and this time represents "half past one, plus 22 and one-tenth seconds, PM":
13:30:22.10
The actual length of a TIME depends on the fractional
seconds precision. These two <data type> specifications have a length of 8
positions:
TIME TIME(0)
This <data type> specification has a length of 10 positions:
TIME(1) -- 8 plus decimal point plus 1 digit in fractional seconds precision
This <data type> specification has a length of 15 positions:
TIME(6)
[Obscure Rule] TIME has a time zone offset equal to the
current default time zone offset of the SQL-session: it represents a local
time.
Here is an example of TIME:
CREATE TABLE time_table_1 ( start_time_1 TIME, start_time_2 TIME(2)); INSERT INTO time_table_1 (start_time_1, start_time_2) VALUES (TIME '14:14:14', TIME '14:14:14.00'); INSERT INTO time_table_1 (start_time_1, start_time_2) VALUES (TIME '15:15:15.', TIME '15:15:15.10'); INSERT INTO time_table_1 (start_time_1, start_time_2) VALUES (TIME '16:16:16.00', TIME '16:16:16.05');
If you want to restrict your code to Core SQL, don't define your
TIME <data type>s with a fractional seconds precision and
don't add the optional noise words WITHOUT TIME ZONE: use
only TIME, never
TIME(x) WITHOUT TIME ZONE.
TIME WITH TIME ZONE
[Obscure Rule] applies for this entire section.
The required syntax for a TIME WITH TIME ZONE <data type>
specification is:
TIME WITH TIME ZONE ::= TIME [ (fractional seconds precision) ] WITH TIME ZONE
TIME WITH TIME ZONE combines the datetime fields
HOUR, MINUTE, SECOND,
TIMEZONE_HOUR and TIMEZONE_MINUTE; it
defines a set of correctly formed values that represent any valid time of day
(based on a 24 hour clock) between '00:00:00' and (at a
minimum) '23:59:61.999999' with a time zone offset that must
be between '-12:59' and '+13:00'. (The
SQL Standard requires DBMSs to allow for the addition of up to 2 "leap" seconds
in a valid time.) TIME WITH TIME ZONE has a length of at
least 14 positions.
As with TIME, the optional fractional seconds precision for
TIME WITH TIME ZONE specifies the number of digits
following the decimal point in the SECOND datetime field.
The minimum fractional seconds precision and the default fractional seconds
precision are both zero.
[NON-PORTABLE] The maximum fractional seconds precision for
TIME WITH TIME ZONE (a) may not be less than 6 digits
and (b) must be equal to the maximum allowed for the
TIME, TIMESTAMP and
TIMESTAMP WITH TIME ZONE <data type>s but is non-standard
because the SQL Standard requires implementors to define
TIME WITH TIME ZONE's maximum fractional seconds precision.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
the fractional seconds precision of
TIME WITH TIME ZONE to range from 0 to 6 digits.
TIME WITH TIME ZONE expects times to have the following
form:
hh:mm:ss[.nnnnnn ][{+|-}HH:MM ]e.g.: the following time represents "half past one, plus 22 seconds, PM" with a time zone offset of 2 and a half hours:
13:30:22+02:30
The actual length of a TIME WITH TIME ZONE depends on the
fractional seconds precision. These two <data type> specifications have a
length of 14 positions:
TIME WITH TIME ZONE TIME(0) WITH TIME ZONE
This <data type> specification has a length of 16 positions:
TIME(1) WITH TIME ZONE -- 14 plus decimal point plus 1 digit in fractional seconds precision
This <data type> specification has a length of 21 positions:
TIME(6) WITH TIME ZONE
[Obscure Rule] TIME WITH TIME ZONE has a time zone offset
equal to the <time zone interval> specified for a given time value: it
represents a time in the given time zone. If the <time zone interval> is
omitted from a given time value, TIME WITH TIME ZONE has a
time zone offset equal to the default time zone offset of the SQL-session: it
represents a local time. The default time zone offset is the <time zone
interval> specified in the most recent SET TIME ZONE
statement issued during the SQL-session. If you haven't issued a
SET TIME ZONE statement, the default time zone offset is
your DBMS's initial default time zone offset.
[NON-PORTABLE] The default time zone offset is non-standard because
the SQL Standard requires implementors to define the initial default time zone
offset for an SQL-session.
[OCELOT Implementation] The OCELOT DBMS that comes with this book
sets the SQL-session's initial default time zone offset to
INTERVAL +'00:00' HOUR TO MINUTE
— this represents UTC.
Here is an example of TIME WITH TIME ZONE:
CREATE TABLE time_table_2 ( start_time_1 TIME WITH TIME ZONE, start_time_2 TIME(2) WITH TIME ZONE); INSERT INTO time_table_2 (start_time_1, start_time_2) VALUES (TIME '14:14:14+03:00', TIME '14:14:14.00+03:00'); INSERT INTO time_table_2 (start_time_1, start_time_2) VALUES (TIME '15:15:15.-03:00', TIME '15:15:15.10-03:00'); INSERT INTO time_table_2 (start_time_1, start_time_2) VALUES (TIME '16:16:16.00+03:30', TIME '16:16:16.05+03:30');
If you want to restrict your code to Core SQL, don't use
TIME WITH TIME ZONE <data type>s.
TIMESTAMP
The required syntax for a TIMESTAMP <data type>
specification is:
TIMESTAMP ::= TIMESTAMP [ (fractional seconds precision) ][ WITHOUT TIME ZONE ]
TIMESTAMP (or
TIMESTAMP WITHOUT TIME ZONE) combines the datetime fields
YEAR, MONTH, DAY,
HOUR, MINUTE and
SECOND; it defines a set of correctly formed values that
represent any valid Gregorian calendar date between
'0001-01-01' and '9999-12-31' (i.e.:
between January 1, 1 AD and December 31, 9999 AD) combined with any valid time
of day (based on a 24 hour clock) between '00:00:00' and (at
a minimum) '23:59:61.999999'. (The SQL Standard requires
DBMSs to allow for the addition of up to 2 "leap" seconds in a valid time.) It
has a length of at least 19 positions.
The optional fractional seconds precision, if specified, is an unsigned integer
that specifies the number of digits following the decimal point in the
SECOND datetime field. The minimum fractional seconds
precision is zero. The default fractional seconds precision is 6. For example,
this <data type> specification defines a set of timestamps with a fractional
seconds precision of zero digits:
TIMESTAMP(0) -- would contain values like '1994-07-15 13:30:22'
These two <data type> specifications both define a set of timestamps with a fractional seconds precision of 6 digits, i.e.: of one-millionth of a second:
TIMESTAMP -- would contain values like '1994-07-15 13:30:22.999999' TIMESTAMP(6) -- would also contain values like '1994-07-15 13:30:22.999999'
[NON-PORTABLE] The maximum fractional seconds precision for
TIMESTAMP (a) may not be less than 6 digits and (b)
must be equal to the maximum allowed for the TIME,
TIME WITH TIME ZONE and
TIMESTAMP WITH TIME ZONE <data type>s but is non-standard
because the SQL Standard requires implementors to define
TIMESTAMP's maximum fractional seconds precision.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
the fractional seconds precision of TIMESTAMP to range
from 0 to 6 digits.
Any operation that attempts to make a TIMESTAMP <data type>
contain a YEAR value that is either less than 1 or greater
than 9999 will fail: the DBMS will return the
SQLSTATE error 22007 "data exception-invalid datetime format".
TIMESTAMP expects timestamps to have the following form:
yyyy-mm-dd hh:mm:ss[.nnnnnn]
e.g.: these two timestamps both represent "half past one, plus 22 seconds, PM on July 15, 1994":
1994-07-15 13:30:22 1994-07-15 13:30:22.00
and this timestamp represents "half past one, plus 22 and one-tenth seconds, PM on July 15, 1994":
1994-07-15 13:30:22.10
Note the mandatory space between the date portion and the time portion of the timestamps.
The actual length of a TIMESTAMP depends on the fractional
seconds precision. This <data type> specification has a length of 19 positions:
TIMESTAMP(0)
This <data type> specification has a length of 21 positions:
TIMESTAMP(1) -- 19 plus decimal point plus 1 digit in fractional seconds precision
These two <data type> specifications both have a length of 26 positions:
TIMESTAMP TIMESTAMP(6)
[Obscure Rule] TIMESTAMP has a time zone offset equal to the
current default time zone offset of the SQL-session: it represents a local
timestamp.
Here is an example of TIMESTAMP:
CREATE TABLE timestamp_table_1 ( start_timestamp_1 TIMESTAMP, start_timestamp_2 TIMESTAMP(2)); INSERT INTO timestamp_table_1 (start_timestamp_1, start_timestamp_2) VALUES ( TIMESTAMP '1997-04-01 14:14:14.999999', TIMESTAMP '1994-07-15 15:15:15.15');
If you want to restrict your code to Core SQL, don't define your
TIMESTAMP <data type>s with a fractional seconds precision
other than 0 or 6 and don't add the optional noise words
WITHOUT TIME ZONE: use only TIMESTAMP,
TIMESTAMP(0) or TIMESTAMP(6), never
TIMESTAMP(x) WITHOUT TIME ZONE.
TIP: Consider using a TIMESTAMP to store time-of-day values
if you plan on doing time arithmetic:
TIMESTAMP '1000-01-01 13:45:00' instead of
TIME '13:45:00'. Although this wastes space on a
meaningless date value, your time arithmetic will be more meaningful, since any
"carries" or "borrows" will show up in the results.
TIMESTAMP WITH TIME ZONE
[Obscure Rule] applies for this entire section.
The required syntax for a TIMESTAMP WITH TIME ZONE <data
type> specification is:
TIMESTAMP WITH TIME ZONE ::= TIMESTAMP [ (fractional seconds precision) ] WITH TIME ZONE
TIMESTAMP WITH TIME ZONE combines the datetime fields
YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND,
TIMEZONE_HOUR and TIMEZONE_MINUTE; it
defines a set of correctly formed values that represent any valid Gregorian
calendar date between '0001-01-01' and
'9999-12-31' (i.e.: between January 1, 1 AD and December
31, 9999 AD) combined with any valid time of day (based on a 24 hour clock)
between '00:00:00' and (at a minimum)
'23:59:61.999999' with a time zone offset that must be
between '-12:59' and '+13:00'. (The SQL
Standard requires DBMSs to allow for the addition of up to 2 "leap" seconds in
a valid time.) It has a length of at least 25 positions.
As with TIMESTAMP, the optional fractional seconds precision
for TIMESTAMP WITH TIME ZONE specifies the number of digits
following the decimal point in the SECOND datetime field.
The minimum fractional seconds precision is zero. The default fractional
seconds precision is 6.
[NON-PORTABLE] The maximum fractional seconds precision for
TIMESTAMP WITH TIME ZONE (a) may not be less than 6
digits and (b) must be equal to the maximum allowed for the
TIME, TIME WITH TIME ZONE and
TIMESTAMP <data type>s but is non-standard because the SQL
Standard requires implementors to define
TIMESTAMP WITH TIME ZONE's maximum fractional seconds
precision.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
the fractional seconds precision of
TIMESTAMP WITH TIME ZONE to range from 0 to 6 digits.
Any operation that attempts to make a
TIMESTAMP WITH TIME ZONE <data type> contain a
YEAR value that is either less than 1 or greater than 9999
will fail: the DBMS will return the
SQLSTATE error 22007 "data exception-invalid datetime format".
TIMESTAMP WITH TIME ZONE expects timestamps to have the
following form:
yyyy-mm-dd hh:mm:ss[.nnnnnn ][{+|-}HH:MM ]e.g.: the following timestamps all represent "half past one, plus 22 seconds, PM on July 15, 1994" with a time zone offset of 2 and a half hours:
1994-07-15 13:30:22+02:30 1994-07-15 13:30:22.+02:30 1994-07-15 13:30:22.00+02:30
The actual length of a TIMESTAMP WITH TIME ZONE depends on
the fractional seconds precision. This <data type> specification has a length
of 25 positions:
TIMESTAMP(0) WITH TIME ZONE
This <data type> specification has a length of 27 positions:
TIMESTAMP(1) WITH TIME ZONE -- 25 plus decimal point plus 1 digit in fractional seconds precision
These two <data type> specifications both have a length of 32 positions:
TIMESTAMP WITH TIME ZONE TIMESTAMP(6) WITH TIME ZONE
[Obscure Rule] TIMESTAMP WITH TIME ZONE has a time zone
offset equal to the <time zone interval> specified for a given timestamp value:
it represents a timestamp in the given time zone. If the <time zone interval>
is omitted from a given timestamp value,
TIMESTAMP WITH TIME ZONE has a time zone offset equal to
the default time zone offset of the SQL-session: it represents a local
timestamp. The default time zone offset is the <time zone interval> specified
in the most recent SET TIME ZONE statement issued during the
SQL-session. If you haven't issued a SET TIME ZONE
statement, the default time zone offset is your DBMS's initial default time
zone offset.
[NON-PORTABLE] The default time zone offset is non-standard because the SQL
Standard requires implementors to define the initial default time zone offset
for an SQL-session.
[OCELOT Implementation] The OCELOT DBMS that comes with this book
sets the SQL-session's initial default time zone offset to
INTERVAL +'00:00' HOUR TO MINUTE
— this represents UTC.
Here is an example of TIMESTAMP WITH TIME ZONE:
CREATE TABLE timestamp_table_2 ( start_timestamp_1 TIMESTAMP WITH TIME ZONE, start_timestamp_2 TIMESTAMP(2) WITH TIME ZONE); INSERT INTO timestamp_table_2 (start_timestamp_1, start_timestamp_2) VALUES ( TIMESTAMP '1997-04-01 14:14:14.999999-03:00', TIMESTAMP '1994-07-15 14:14:14.35+02:15');
If you want to restrict your code to Core SQL, don't use
TIMESTAMP WITH TIME ZONE <data type>s.
Interval <data type>s
An interval <data type> is defined by a descriptor that contains two pieces of information:
- The <data type>'s name:
INTERVAL. - The <data type>s <interval qualifier>, which specifies the type of interval and the precision of the interval's set of valid values.
INTERVAL
The required syntax for an INTERVAL <data type>
specification is:
INTERVAL ::= INTERVAL
INTERVAL is a span of time; it defines a set of correctly
formed values that represent any span of time compatible with the <interval
qualifier>. It combines the datetime fields YEAR and/or
MONTH if it is a year-month interval. It combines the
datetime fields DAY and/or HOUR and/or
MINUTE and/or SECOND if it is a day-time
interval. It has a length of at least 1 position.
A year-month INTERVAL combines one or more of the datetime
fields YEAR and MONTH in the <interval
qualifier>. The possible definitions are thus:
INTERVAL YEAR [ (leading precision) ] INTERVAL MONTH [ (leading precision) ] INTERVAL YEAR [ (leading precision) ] TO MONTH
The leading precision, if specified, is as described in "<interval qualifier>".
The values of the "start_datetime" field are constrained only by the leading
precision of that field. The month value in
INTERVAL YEAR TO MONTH represents an additional number of
months (within years) and can thus range only from 0 to 11.
INTERVAL YEAR expects intervals to have the following form:
'y[...]'
e.g.: '20' represents a span of 20 years.
INTERVAL YEAR has a length of "leading precision"
positions. For example, this <data type> specification has a length of 4
positions:
INTERVAL YEAR(4)
INTERVAL MONTH expects intervals to have the following
form:
'm[...]'
e.g.: '15' represents a span of 15 months.
INTERVAL MONTH has a length of "leading precision"
positions. For example, this <data type> specification has a length of 2
positions:
INTERVAL MONTH
(The default precision is 2 digits.)
INTERVAL YEAR TO MONTH expects intervals to have the
following form:
'y[...]-mm'
e.g.: '20-03' represents a span of 20 years plus 3 months.
INTERVAL YEAR TO MONTH has a length of "leading precision"
plus 3 positions. For example, this <data type> specification has a length of 5
positions:
INTERVAL YEAR TO MONTH
A day-time INTERVAL combines one or more of the datetime
fields DAY, HOUR,
MINUTE and SECOND in the <interval
qualifier>. The possible definitions are thus:
INTERVAL DAY [ (leading precision) ] INTERVAL HOUR [ (leading precision) ] INTERVAL MINUTE [ (leading precision) ] INTERVAL SECOND [ (leading precision [ ,fractional seconds precision ]) ] INTERVAL DAY [ (leading precision) ] TO HOUR INTERVAL DAY [ (leading precision) ] TO MINUTE INTERVAL DAY [ (leading precision) ] TO SECOND [ (fractional seconds precision) ] INTERVAL HOUR [ (leading precision) ] TO MINUTE INTERVAL HOUR [ (leading precision) ] TO SECOND [ (fractional seconds precision) ] INTERVAL MINUTE [ (leading precision) ] TO SECOND [ (fractional seconds precision) ]
The leading precision, if specified, is as described in "<interval
qualifier>". The values of the "start_datetime" field are constrained only by
the leading precision of that field. The hour value in
INTERVAL DAY TO HOUR,
INTERVAL DAY TO MINUTE and
INTERVAL DAY TO SECOND represents an additional number of
hours (within days) and can thus range only from 0 to 23. The minute value in
INTERVAL DAY TO MINUTE,
INTERVAL DAY TO SECOND,
INTERVAL HOUR TO MINUTE and
INTERVAL HOUR TO SECOND represents an additional number of
minutes (within hours) and can thus range only from 0 to 59. The seconds value
in INTERVAL DAY TO SECOND,
INTERVAL HOUR TO SECOND and
INTERVAL MINUTE TO SECOND represents an additional number
of seconds and fractions of a second (within minutes) and can thus range only
from 0 to 59.9n (where ".9n" represents the number of digits defined for the
fractional seconds precision). The fractional seconds precision, if specified,
is as described in "<interval qualifier>".
INTERVAL DAY expects intervals to have the following form:
'd[...]'
e.g.: '1' represents a span of 1 day.
INTERVAL DAY has a length of "leading precision" positions.
For example, this <data type> specification has a length of 2 positions:
INTERVAL DAY
(The default precision is 2 digits.)
INTERVAL HOUR expects intervals to have the following form:
'h[...]'
e.g.: '15' represents a span of 15 hours. INTERVAL HOUR has
a length of "leading precision" positions. For example, this <data type>
specification has a length of 2 positions:
INTERVAL HOUR
INTERVAL MINUTE expects intervals to have the following
form:
'm[...]'
e.g.: '75' represents a span of 75 minutes. INTERVAL MINUTE
has a length of "leading precision" positions. For example, this <data type>
specification has a length of 2 positions:
INTERVAL MINUTE
INTERVAL SECOND expects intervals to have the following
form:
's[...[.n...]]'
e.g.: '1' represents a span of 1 second,
'20' and '20.0' both represent a span of
20 seconds and '20.5' represents a span of 20.5 seconds.
INTERVAL SECOND has a length of "leading precision" plus
"fractional seconds precision" positions. For example, this <data type>
specification has a length of 2 positions:
INTERVAL SECOND(0)
These two <data type> specifications both have a length of 9 positions:
INTERVAL SECOND INTERVAL SECOND(6)
(The default fractional seconds precision is 6 digits. A fractional seconds precision greater than zero includes one position for the decimal point.)
INTERVAL DAY TO HOUR expects intervals to have the
following form:
'd[...] h[...]'
e.g.: '1 1' represents a span of 1 day plus 1 hour and
'20 10' represents a span of 20 days plus 10 hours. (Note
the mandatory space between the days portion and the hours portion of the
interval.) INTERVAL DAY TO HOUR has a length of "leading
precision" plus 3 positions. For example, this <data type> specification has a
length of 5 positions:
INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE expects intervals to have the
following form:
'd[...] h[...]:m[...]'
e.g.: '1 1:1' represents a span of 1 day, 1 hour plus 1
minute and '20 10:15' represents a span of 20 days, 10 hours
plus 15 minutes. (Note the mandatory colon between the hours portion and the
minutes portion of the interval.) INTERVAL DAY TO MINUTE has
a length of "leading precision" plus 6 positions. For example, this
<data type> specification has a length of 8 positions:
INTERVAL DAY TO MINUTE
INTERVAL DAY TO SECOND expects intervals to have the
following form:
'd[...] h[...]:m[...]:s[...[.n...]]'
e.g.: '1 1:1:1' and '01 01:01:01.00' both
represent a span of 1 day, 1 hour, 1 minute plus 1 second and
'20 10:15:20.5' represents a span of 20 days, 10 hours, 15
minutes plus 20.5 seconds. (Note the mandatory colon between the minutes
portion and the seconds portion of the interval.)
INTERVAL DAY TO SECOND has a length of "leading precision"
plus "fractional seconds precision" plus 9 positions. For example, this <data
type> specification has a length of 11 positions:
INTERVAL DAY TO SECOND(0)
These two <data type> specifications both have a length of 18 positions:
INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND(6)
INTERVAL HOUR TO MINUTE expects intervals to have the
following form:
'h[...]:m[...]'
e.g.: '10:15' represents a span of 10 hours plus 15 minutes.
INTERVAL HOUR TO MINUTE has a length of "leading precision"
plus 3 positions. For example, this <data type> specification has a length of 5
positions:
INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO SECOND expects intervals to have the
following form:
'h[...]:m[...]:s[...[.n...]]'
e.g.: '10:15:20.5' represents a span of 10 hours, 15 minutes
plus 20.5 seconds. INTERVAL HOUR TO SECOND has a length of
"leading precision" plus "fractional seconds precision" plus 6 positions. For
example, this <data type> specification has a length of 8 positions:
INTERVAL HOUR TO SECOND(0)
These two <data type> specifications both have a length of 15 positions:
INTERVAL HOUR TO SECOND INTERVAL HOUR TO SECOND(6)
INTERVAL MINUTE TO SECOND expects intervals to have the
following form:
'm[...]:s[...[.n...]]'
e.g.: '15:20.5' represents a span of 15 minutes plus 20.5
seconds and '14:15' represents a span of 14 minutes plus 15
seconds. INTERVAL MINUTE TO SECOND has a length of "leading
precision" plus "fractional seconds precision" plus 3 positions. For example,
this <data type> specification has a length of 5 positions:
INTERVAL MINUTE TO SECOND(0)
These two <data type> specifications both have a length of 12 positions:
INTERVAL MINUTE TO SECOND INTERVAL MINUTE TO SECOND(6)
Here is an example of INTERVAL:
CREATE interval_table ( interval_column_1 INTERVAL YEAR(3) TO MONTH, interval_column_2 INTERVAL DAY TO MINUTE, interval_column_3 INTERVAL MINUTE TO SECOND(4)); INSERT INTO interval_table ( interval_column_1, interval_column_2, interval_column_3) VALUES ( INTERVAL '150-01' YEAR TO MONTH, INTERVAL '-36 22:30' DAY TO MINUTE, INTERVAL -'15:22.0001' MINUTE TO SECOND(4));
If you want to restrict your code to Core SQL, don't use the
INTERVAL <data type>.
Now that we've described SQL's datetime <data type>s, let's look at some example SQL statements that put them to use.
These SQL statements make a Table with a date Column, insert a row, then search for any date after January 2nd, 2000.
CREATE TABLE Date_Examples ( occurrence_date DATE); INSERT INTO Date_Examples (occurrence_date) VALUES (DATE '2001-02-29'); SELECT occurrence_date FROM Date_Examples WHERE occurrence_date > DATE '2000-01-02';
These SQL statements make a Table with two time-of-day Columns, insert a row, then search for any time before 8:30 PM.
CREATE TABLE Time_Examples ( occurrence_time TIME, occurrence_time_zone TIME WITH TIME ZONE); INSERT INTO Time_Examples (occurrence_time, occurrence_time_zone) VALUES (TIME '12:00:00', TIME '12:00:00+3:00'); SELECT occurrence_time, occurrence_time_zone FROM Time_Examples WHERE occurrence_time < TIME '20:30:00';
These SQL statements make a Table with two timestamp Columns, insert a row, then search for any timestamp equal to January 2nd, 2000 at 1 second past midnight.
CREATE TABLE Timestamp_Examples ( occurrence_timestamp TIMESTAMP, occurrence_timestamp_zone TIMESTAMP WITH TIME ZONE); INSERT INTO Timestamp_Examples ( occurrence_timestamp, occurrence_timestamp_zone) VALUES ( TIMESTAMP '2001-02-29 16:00:00', TIMESTAMP '2001-02-29 16:00:00+0:00'); SELECT occurrence_timestamp, occurrence_timestamp_zone FROM Timestamp_Examples WHERE occurrence_timestamp_zone = TIMESTAMP '2000-01-02 00:00:01';
These SQL statements make a Table with two year-month interval Columns, insert a row, then search for any interval that is less than or equal to 37 months.
CREATE TABLE YInterval_Examples ( occurrence_interval_1 INTERVAL YEAR, occurrence_interval_2 INTERVAL YEAR TO MONTH); INSERT INTO YInterval_Examples ( occurrence_interval_1, occurrence_interval_2) VALUES ( INTERVAL '3' YEAR, INTERVAL '02-10' YEAR TO MONTH'); SELECT occurrence_interval_1, occurrence_interval_2 FROM YInterval_Examples WHERE occurrence_interval_1 <= INTERVAL '37' MONTH;
These SQL statements make a Table with two day-time interval Columns, insert two rows, then search for any interval that doesn't equal 30 seconds.
CREATE TABLE DInterval_Examples ( occurrence_interval_1 INTERVAL SECOND, occurrence_interval_2 INTERVAL SECOND(2,4)); INSERT INTO DInterval_Examples ( occurrence_interval_1, occurrence_interval_2) VALUES ( INTERVAL '25.000005' SECOND, INTERVAL '25.0001' SECOND'); INSERT INTO DInterval_Examples ( occurrence_interval_1, occurrence_interval_2) VALUES ( INTERVAL '22' SECOND, INTERVAL '22' SECOND'); SELECT occurrence_interval_1, occurrence_interval_2 FROM DInterval_Examples WHERE occurrence_interval_1 <> INTERVAL '30' SECOND;
