Date and time data types

The current version of OceanBase Database supports the following date and time data types: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Temporal values represented by each type have a valid value range.

When you use these date and time data types, take note of the following items:

• OceanBase Database generates date and time data in the standard format, but supports parsing the input date and time data in different formats.

• An input date literal must be in the year-month-day order, instead of the month-day-year or day-month-year order. For example, you can enter 98-09-04, but not 09-04-98 or 04-09-98. To convert a string in an incorrect order into the correct order, use the STR_TO_DAT function.

• A two-digit year is sometimes ambiguous, because the century cannot be determined. To solve this problem, OceanBase Database converts the year value based on the following principle: If the value is from 70 to 99, it is converted into the corresponding year from 1970 to 1999. If the value is from 00 to 69, it is converted into the corresponding year from 2000 to 2069.

• If you convert data of the DATE type to the DATETIME or TIMESTAMP type, the time in the new data is 00:00:00. If you convert data of the TIME type to the DATETIME or TIMESTAMP type, the date in the new data is the current date of the system. This date is determined by the system variable timestamp.

• If temporal values are used in numeric operations such as addition, OceanBase Database automatically converts the temporal values into numeric values. Numeric values can also be converted into temporal values when necessary.

• In most cases, when a temporal value is out of range or invalid, OceanBase Database converts the value into the "zero" value of the corresponding date and time data type. For example, if the value is of the DATETIME type, the conversion result is 0000-00-00 00:00:00. If the value is of the DATE type, the conversion result is 0000-00-00. However, an out-of-range TIME value is clipped to the appropriate endpoint of the TIME range.

• By setting the SQL mode, you can determine which types of dates you want OceanBase Database to support. For example, you can enable the ALLOW_INVALID_DATES mode to support possibly invalid dates such as 2009-11-31. In this case, OceanBase Database checks only whether the month is in the range from 1 to 12 and the day is in the range from 1 to 31. You can also disable the NO_ZERO_IN_DATE mode to support dates in which the month or day is zero, such as 2020-00-01 and 2020-01-00. In this case, expected results may not be returned for the DATE_SUB or DATE_ADD function.

• OceanBase Database allows the "zero" value of date, which is 0000-00-00. For data of the DATETIME or TIMESTAMP type, the value of the minute and second parts must also be 0.

The following table lists the "zero" value of each date and time data type. You can explicitly use the values in the table to represent "zero" values. You can also just use the value '0' or 0, which is easier to write.