Follow me. sql: (ix) datetime and interval data types

Data | data type datetime and interval are two types of data that are related to time. Their role is reflected in the creation or alteration of a record in a record library, the time it takes to run a record when a time occurs, or when a DateTime variable has been established. This article describes the two data types described by the SQL92 standard.




SQL data type

If you want to learn more about SQL data types, read the SQL Basics: String data types, which cover common data types in SQL and illustrate the use of various string data types, SQL Basics: Numeric data types detail the numeric data types.



Datetime
Data types used to represent time or date are of datetime type. Each datetime data type has its own means of obtaining the length of the value and the information it holds, such as days, months, minutes, seconds, decimals of seconds, and so on.

In fact, the implementation of DateTime has different lengths and formats depending on the criteria that define it; However, the types defined by each company are inherently compliant with the following rules. For example, an implementation of prominent peculiarity (timestamp) may not have a separator, and as the specification of the detail varies, the length and format change, and in some cases whitespace is used as a spacer. datetime data types include: data, time, and timestamp. Let's take a closer look at these classifications, and first let's examine data.

DATA
Usage: DATA

The data type allows no parameters, such as precision.
The fields for data include year, month, and day.
The length of the data is 10 characters: Yyyy-mm-dd. (Y represents the year, M represents the month, and D represents the day.) ）
It only allows comparisons with other data type fields.
The allowed numbers must conform to the Gregorian specification.
Time
Usage: Time (precision)

The type contains hours, minutes, and seconds, formatted as HH:MM:SS (h for hours, m for minutes, s for seconds)
Precision can be selected, (...). ）
The time is the world standard Time (Universal coordinated TIME,UTC), that is 00:00:00. Midnight in Greenwich, the server's time zone is implied.
If you do not need a fraction of a second, the length of time is eight characters. Otherwise, the eight-bit length is added with precision: HH:MM:SS.P.
It can only be compared with other time type data.
If no precision is specified, the precision defaults to 0.

Time and Time ZONE


Usage: Time (precision) with time ZONE

This value should conform to the time data type
The TimeZone section represents the time difference relative to UTC: 00:00:00+hh:mm. Its range is 12:59 to 13:00.
The precision represents the decimal part of a second.
The time length with TimeZone is 14 characters plus precision, plus a separator.
can only be compared with time type data with timezone
TIMESTAMP

Usage: TIMESTAMP (precision)

This type contains a period, month, day, time, minute, and second, in the format: Yyyy-mm-dd hh:mm:ss.
Can include a fraction of a second, which is determined by the precision of the definition.
Its date part conforms to the Gregorian standard, and the time section is in UTC format. The default is the local time zone.
The length of the time scale is 19 characters plus precision, plus the precision separator.
Many systems deviate from the length defined above, such as the UNIX-style time scale format: Yyyy-mm-dd HH:MM:SS.P.
If no precision is defined, the default value is 6, but many database companies default to 0, so please refer to your development documentation.
Prominent peculiarity can only be compared to the values of other timestamp types.
TIMESTAMP with Time ZONE

Usage: TIMESTAMP (precision) with time ZONE

The time scale part conforms to the above TIMESTAWP rules.
The precision represents the fraction of a second.
The time zone portion of the requirement is the same as time zone, that is, the timezone conforms to the UTC specification and ranges between -12:59 and +13:00.
The total length is 25 characters plus precision, plus a precision separator: Yyyy-mm-dd HH:MM:SS.P.
It can only be compared with other timestamp with the time zone type of data.
Interval

Interval is used to represent time scales. For example, you can use the operator (which will be explained below) to calculate the days of two days and save them.

Companies are very different in dealing with interval-some companies offer different units of measure, such as years or minutes, and some companies do not support interval at all. The SQL92 standard INTERVAL type provides only one subtype: INTERVAL.


INTERVAL

Usage: INTERVAL (qualifier)

There are two types of interval: The year-month, the year and month (YYYY-MM), and the day-time (DD HH:MM:SS), which is used to hold days, hours, minutes, and seconds.
Qualifier--Interval the leading precision (lead precision) in some databases to indicate whether the interval adopts a "year-month" or "Day-time" method depending on its value.
Interval can be positive and negative.
When compared with other interval type variables, the results are kept to the maximum precision and, if necessary, 0.
Interval are all made up of integers except seconds that contain decimals.
The interval variable of the year-month type can only be compared to its interval variable of year-month. The day-time type is similar to this.
Operator

Action result type

When processing datetime, the time zone remains unchanged-although some databases convert one of the time zones to another for comparison purposes. There are some operational keywords, such as overlaps and extract, that are used to manipulate and compare datetime type data. However, different databases differ significantly in the way they are used and supported by these operational keywords.

Overlaps is used to compute the span of a time overlap, with an action object that can be two datetime or a datetime and a interval. Extract is used to extract a portion of a datetime or interval type of data, such as extracting a month from the data type.

Data type
Since the established standards are for companies to implement actual data types on an existing basis, you still need to refer to your database documentation frequently in terms of specific constraints, parameters, and data types.