Many RDBMS systems have their own functions for date/time math.
You can generally perform the following operations:> Subtract two date/times to get an interval.
> Add or subtract an interval and a date/time to get a future or past date/time.
> Add or subtract two Intervals to get a new Interval.
> Multiply or divide an Interval by a number to get a new Interval.

Adding two dates makes no sense.

EXTRACT() can be used to retrieve a single field from a date, time, or timestamp.

> Access and SQL Server have a DATEDIFF() function.
> MySQL has DATE_ADD() and DATE_SUB() functions.
> Oracle has an ADD_MONTHS() function.

If precision is specified, it shows the number of positions in the field:

INTERVAL HOUR(2)                     Means 2 digits for the hour.
                                     This is also the default length for HOUR
                                     if precision is omitted.

INTERVAL SECOND(5,3)                 Means 5 digits for the second
                                     and 3 digits for the fractional second.
                                     6 is the default fractional precision of second
                                     if the fractionalprecision is omitted.

INTERVAL DAY(5) TO MINUTE            Means 5 digits for DAY,
                                     and values of HOUR and MINUTE.

INTERVAL MINUTE(5) TO SECOND(4)      Means 5 digits for MINUTE and 4 digits for SECOND.

INTERVAL '21:17:7.2' 21 hours,       17 minutes,7.2 seconds.

INTERVAL '21:17:7.2' HOUR TO SECOND  21 hours, 17 minutes, 7.2 seconds.

Changing a time with an INTERVAL:

NewTime = DueTime + INTERVAL '30' MINUTE

Finding the number of hours between two times:

TimeLate = (TimePaid - DueTime) HOUR

A time can have it’s time zone specified:

TIME '15:45:30'                         AT LOCAL (AT LOCAL means that
                                        this time is in the local time zone.)

INTERVAL '10' DAY + INTERVAL '15' DAY   Gives an interval of '25' days.

The day-time interval has to do with the difference between times and contains a day value, an hour value, a minute value, a second value, or some combinations of these.

Some sample formats are:

INTERVAL DAY
INTERVAL DAY(precision)
INTERVAL HOUR
INTERVAL HOUR(precision)
INTERVAL MINUTE
INTERVAL MINUTE(precision)
INTERVAL SECOND
INTERVAL SECOND(fractional_precision)
INTERVAL DAY TO HOUR
INTERVAL DAY(precision) TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL DAY(precision) TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL DAY(precision) TO SECOND
INTERVAL DAY(precision) TO SECOND(fractional_precision)
INTERVAL HOUR TO SECOND
...
INTERVAL HOUR(precision) TO SECOND
...
INTERVAL MINUTE(precision) TO SECOND(fractional_precision)
...
etc.

The year-month interval has to do with the difference between dates and contains a year value, a month value, or both.

The formats are:

INTERVAL YEAR
INTERVAL YEAR(precision)
INTERVAL MONTH
INTERVAL MONTH(precision)
INTERVAL YEAR TO MONTH
INTERVAL YEAR(precision) TO MONTH

Examples:

INTERVAL '12-7'                  12 years and 7 months.
INTERVAL '12-7' YEAR TO MONTH    12 years and 7 months.

Changing a date with an INTERVAL:

NewDate = DueDate + INTERVAL '7' DAY

Finding the number of days between two dates:

DaysLate = (DatePaid - DueDate) DAY
(DATE '2056-03-20' - DATE '2055-01-10') YEAR TO MONTH = '0001-02' (Or 1 year and 2 months.)

An interval is the difference between two datetime values.
In many applications that use dates and times, you sometimes need to determine the interval between two dates or times.
An interval contains the amount of time between two date/time values.
An interval can be used to increase or decrease the value of a date or time.
While various RDBMS systems have their own unique functions to handle these intervals, SQL defines two distinct types of intervals: the year-month interval and the day-time interval.A year-month interval contains the number of whole years and months between two dates.

A day-time interval contains the number of days, hours, minutes, and seconds between two times.

A multiple field qualifier is stated:           INTERVAL start_field TO end_field
where start_field can be YEAR, DAY, HOUR, or MINUTE,
and the end_field can be YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.
Start_field must be smaller than end_field.
Start_field can have a precision, but end_field can only have a fracional-precision on SECOND.

> You can not add two dates because it makes no sense and SQL will not allow it.
> You can do calculations with intervals.
> Intervals can be added or subtracted.
> Interval field separators are the same as date/time field separators.
> When doing calculations, you can not mix year-month interval calculations with day-time interval calculations because months come in varying lengths of 28, 29, 30, and 31 days.
> INTERVAL is not supported by Access, DB2, MySQL, or SQL Server.

The TIMESTAMP data type is a column definition that contains the date and time as one field. A TIMESTAMP is frequently used for audit records to establish when critical data has been changed. It is stored internally as ‘CCYY-MM-DD HH.MM.SS.MMMMMM’ (up to 26 in length). A timestamp column must have a valid date and time and can not be blank. If the column is defined as allowing NULLs, then NULL should be used for a missing timestamp.

 

Example:    COL1 TIMESTAMP

Sample value is: ‘2056-02-20 17:39.45′

 

Notice that the keyword, TIMESTAMP, is a complete definition of the data type and has no value defined in parentheses. See Sample Data Type Values.

TIMESTAMP WITHOUT TIME ZONE is the same as TIMESTAMP. In this course, we use TIMESTAMP.

TIMESTAMP WITH TIME ZONE is the same as TIME WITH TIME ZONE, it’s just a timestamp with a time zone.

The TIME WITH TIME ZONE data type is stored in the ‘HH:MM:SS.MMMMMM -HH:MM’ format. A time column must have a valid time value and can not be blank. The TIME ZONE component shows the offset of the time zone and can range from -12:59 to +13:00. All other SQL rules are the same as they are with TIME.

 

Example:    COL1 TIME WITH TIME ZONE

Sample value is: ‘17:39.45 -6:00′

 

Notice that the keyword, TIME WITH TIME ZONE, is a complete definition of the data type and has no value defined in parentheses. See Sample Data Type Values.

Like the DATE data type, the TIME data type is stored internally with hours, minutes, seconds, and optionally, with milliseconds in the ‘HH:MM:SS’ (length 8), and ‘HH:MM:SS.MMMMMM’ (up to length 15) format. A time column must have a valid time value and can not be blank. Valid values are from ‘00:00:000000′ to ‘23:59:59.999999′. If the column is defined as allowing NULLs, then NULL should be used for a missing time.

 

Example:    COL1 TIME

Sample value is: ‘17:39.45′

 

Notice that the keyword, TIME, is a complete definition of the data type and has no value defined in parentheses.
See Sample Data Type Values.

TIME WITHOUT TIME ZONE is the same as TIME. In this course, we use TIME.

With DATE, you no longer have to worry about one date field being defined as YYMMDD and another as MMDDYY or MM/DD/YY, and whether they are stored in binary, packed decimal, display, or alphanumeric format. All SQL DATEs are stored the same way internally, so that any DATE column can be compared to any other DATE column.

SQL DATEs can be entered or displayed in several formats, but it is stores internally so that it includes the century, year, month, and day such as ‘CCYY-MM-DD’ (with a length of 10).

A DATE column must have a valid date and can not be blank. If the columns are defined as allowing nulls, then NULL should be used for a missing date. Valid values are from ‘0001-01-01′ to ‘9999-12-31′.

 

Example:    COL1 DATE

 

Notice that the keyword, DATE, is a complete definition of the data type and has no value defined in parentheses.
See Sample Data Type Values.

Sample value is: ‘2056-02-20′