SQL basics: Datetime and interval data types

Datetimes and intervals are time-based data types. These types are useful for recording when a database entry was created or modified, for logging when an event occurred, or for determining how much time has elapsed since another datetime value was created. Here, I will explain the SQL92 standard’s descriptions of these time-tracking types.

Datetimes

Data types that have values representing times or dates are referred to as datetimes. Each datetime data type has its own means for determining the length of the value and for knowing what information is stored, such as day, month, minutes, seconds, fraction of a second, etc.

Actual datetime implementations vary in length and format from the defined standard; however, each manufacturer’s defined type is expected to conform internally to the rules laid out below. For example, a timestamp implementation may have no delimiters, changing both the length and format from the specification details, which does contain delimiters and in some cases, spaces. The datetime data types include DATE, TIME, and TIMESTAMP. Let’s take a closer look at each, beginning with DATE.

DATE
Usage: DATE

• The DATE type allows no parameters, such as precision.
• A DATE field consists of the year, month, and day.
• The length of a DATE value is ten characters: YYYY-MM-DD.
• It can be compared only to other DATE type fields.
• Allowable dates conform to the Gregorian calendar.

TIME
Usage: TIME (precision)

• This type contains hour, minute, and second values: hh:mm:ss.
• It allows the optional precision parameter, dictating the precision to use for values of fractions of a second.
• Time is represented using Universal Coordinated Time (UTC), where 00:00:00 represents midnight in Greenwich, and the server’s local time zone is implied.
• The length of a TIME value is 8 characters, if there are no fractions of a second. Otherwise, the length is 8, plus the precision, plus one for the delimiter: hh:mm:ss.p.
• It can only be compared to other TIME data types.
• If no precision is specified, it is 0 by default.

TIME WITH TIME ZONE
Usage: TIME (precision) WITH TIME ZONE

• The TIME portion of this value conforms to requirements for the TIME data type.
• The TIMEZONE portion of this value represents the offset from UTC: 00:00:00+hh:mm. Its range is between -12:59 and +13:00.
• The precision represents fractions of a second.
• A time with TIMEZONE type’s length is 14 characters plus the precision, plus one for the delimiter.
• It can only be compared to other TIME with TIMEZONE values.

TIMESTAMP
Usage: TIMESTAMP (precision)

• This type contains year, month, day, hour, minute, and second values: YYYY-MM-DD hh:mm:ss.
• It optionally contains fractions of a second, bound by the defined precision.
• It conforms to the Gregorian calendar for the date portion and with UTC for the time format. It also defaults to the server’s local time zone.
• A TIMESTAMP length is nineteen characters, plus the precision, plus one for the precision delimiter: YYYY-MM-DD hh:mm:ss.p.
• Many systems deviate from this defined length, using a UNIX-style  TIMESTAMP  instead: YYYYMMDDhhmmss.
• If no precision is defined, a value of 6 is used by default, but many database manufacturers use 0 by default, so consult your documentation.
• It can only be compared to other TIMESTAMP values.

TIMESTAMP WITH TIME ZONE
Usage: TIMESTAMP (precision) WITH TIME ZONE

• The timestamp portion of this type conforms to the TIMESTAMP rules laid out above.
• The precision represents fractions of a second.
• The time zone portion works the same as for TIME WITH TIME ZONE, where the time zone agrees with UTC and is in the range -12:59 to +13:00.
• The total length is 25 characters, plus the precision, plus one for the precision delimiter: YYYY-MM-DD hh:mm:ss.p.
• It can only be compared to other TIMESTAMP WITH TIME ZONE values.

Intervals
Intervals are used to represent a measure of time. For example, you can use operators (explained below) to find out and store the number of days between two dates.

Manufacturers vary drastically in their handling of intervals—some offer different types for each unit of measure, such as years and minutes, and some databases don’t support intervals at all. The SQL92 interval type definition has only one subtype: INTERVAL.

INTERVAL
Usage: INTERVAL (qualifier)

• There are two types of intervals: year-month, which stores the year and month (YYYY-MM); and day-time (DD HH:MM:SS), which stores the days, hours, minutes, and seconds.
• The qualifier—known in some databases as the interval lead precision—dictates whether the interval is year-month or day-time. Implementation on the qualifier value varies.
• Intervals can be positive or negative.
• When compared to other intervals, the result takes on the greatest precision, padding values with zeros if necessary.
• All INTERVAL components are integers, except seconds, which can contain fractions of a second.
• You can only compare year-month intervals to other year-month intervals. The same goes for day-time values.

Operators
SQL92 outlines various operators that can be used on datetime and interval data types. Review Table A below to find possible combinations and the data types these operations result in.

Operation result types

Time zones are maintained when dealing with date times, although some databases will convert one time zone to another to allow comparisons. Some keyword operators exist, such as OVERLAPS and EXTRACT, to manipulate and compare datetime values. However, database implementations are highly varied in their usage or support.

OVERLAPS can be used with two sets of datetimes or a datetime and an interval to determine at what time span the dates intersect. Use EXTRACT to pull one component out of a datetime or interval type, such as to determine just the month value in a DATE.

Data types
As the standard has been designed for manufacturers to base their actually implemented types on the ones presented here, you should always refer to your database’s documentation for specific limits, parameters, and data type names.