Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the role of datetime and interval data types in SQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

SQL data type

If you want to learn more about SQL data types, please read the basics of SQL: string data types, the scope of this article covers the common data types in SQL and explains the use of various string data types, SQL Foundation: numeric data types detail numeric data types.

Datetime

The data type used to represent the time or date is of type datetime. Each datetime data type has its own means to get the length of the value and the information it holds, such as days, months, minutes, seconds, decimals in seconds, and so on.

In fact, the implementation of datetime varies in length and format depending on the standard in which it is defined; however, the types defined by each company inherently conform to the following rules. For example, an implementation of a time scale (timestamp) may not have a delimiter, and the length and format may change with the specification of the details, with spaces as delimiters in some cases. Datetime data types include: DATA, TIME, and TIMESTAMP. Let's take a closer look at these categories, first let's take a look at DATA.

DATA

Usage: DATA

The DATA type allows no parameters, such as precision.

The fields of DATA include year, month, and day.

The length of DATA is ten characters: YYYY-MM-DD. (y for year, M for month, and D for day. )

It only allows comparisons with other DATA type fields.

The allowed numbers must conform to the specifications of the Gregorian calendar.

TIME

Usage: TIME (precision)

This type contains hours, minutes, and seconds in the format hh:mm:ss (h for hours, m for minutes, s for seconds)

The precision can be selected. )

The time is based on the Universal Standard time (Universal Coordinated Time,UTC), that is, 00:00:00. Midnight in Greenwich, the time zone of the server is implied.

If you do not need a fractional fraction of seconds, the length of TIME is eight characters. Otherwise, the eight-digit length plus 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 WITH TIME ZONE

Usage: TIME (precision) WITH TIME ZONE

This value should match the TIME data type

The TIMEZONE section indicates the time difference relative to UTC: 00:00:00+hh:mm. It ranges from-12:59 to 13:00.

Precision represents the fractional portion of a second.

The length of TIME with TIMEZONE is 14 characters plus precision, plus a delimiter.

Can only be compared with TIME type data with TIMEZONE

TIMESTAMP

Usage: TIMESTAMP (precision)

This type contains years, months, days, hours, minutes, seconds, and the format is: YYYY-MM-DD hh:mm:ss.

Can include a fraction of seconds, which is determined by the precision of the definition.

Its date part conforms to the Gregorian calendar standard, and the time part is in UTC format. Default is the local time zone.

The length of the time scale is 19 characters, plus precision, plus precision separator.

Many systems deviate from the length defined above, for example, the UNIX style time scale format is: YYYY-MM-DD hh:mm:ss.p.

If no precision is defined, the default is 6, but many companies default to 0, so please refer to your development documentation.

Time scales can only be compared to values of other TIMESTAMP types.

TIMESTAMP WITH TIME ZONE

Usage: TIMESTAMP (precision) WITH TIME ZONE

The time scale partially conforms to the above TIMESTAWP rules.

Precision represents the fractional portion of a second.

The requirements for the time zone section are the same as those for TIME WITH TIME ZONE, and the immediate zone conforms to the UTC specification, ranging from-12:59 to + 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 data of other TIMESTAMP WITH TIME ZONE types.

Interval

Interval is used to represent the time scale. For example, you can use the operator (explained below) to calculate the number of days between two days and save them.

Companies vary greatly in dealing with interval-some offer different units of measurement, such as years or minutes, while others do not support interval at all. The interval type of the SQL92 standard provides only one subtype: INTERVAL.

INTERVAL

Usage: INTERVAL (qualifier)

There are two types of interval: year-month, which is the year and month saved (YYYY-MM), and Day-time (DD HH:MM:SS), which is used to save days, hours, minutes, and seconds.

Qualifiers-- interval leading precision (lead precision) in some databases-- indicate whether the interval should be "year-month" or "day-time" based on its value.

Interval can be positive or negative.

When compared with other interval type variables, the results maintain maximum precision and zero padding if necessary.

INTERVAL consists entirely of integers, except for seconds containing decimals.

Interval variables of the year-month type can only be compared with other year-month interval variables. The day-time type is similar.

Operator

Operation result type

When dealing with date and time, the time zone remains the same-- although some databases convert one time zone to another for comparison purposes. There are some action keywords, such as OVERLAPS and EXTRACT, that are used to manipulate and compare datetime type data. However, different databases differ greatly in the usage and support of these operation keywords.

OVERLAPS is used to calculate the span of time overlap, and its operands can be two datetime or a datetime and an interval. EXTRACT is used to extract a portion of datetime or interval type data, such as a month in DATA type data.

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 documents frequently in terms of specific restrictions, parameters, and data types.

About what the role of datetime and interval data types in SQL is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.