Date and time data types

The current version of OceanBase Database supports the DATE, TIME, DATETIME, TIMESTAMP, and YEAR data types for date and time values, with each type having a valid value range.

Consider the following points when you use date and time types:

• OceanBase Database outputs date and time values in a standard format but tries to parse values that you enter in multiple formats.

• OceanBase Database tries to parse the date part of a literal that you enter in multiple formats. However, the date part must be in "yyyy-mm-dd" format, for example, '1998-09-04', and cannot be in "mm-dd-yyyy" or "dd-mm-yyyy" format, for example, '09-04-1998' or '04-09-1998'. To convert a string that is not in "yyyy-mm-dd" format, use the STR_TO_DATE function.

• Years are specified with two digits, which is ambiguous because it is not clear which century they refer to. OceanBase Database converts a one- or two-digit year as follows: years from "00" to "69" are converted to years from "2000" to "2069", and years from "70" to "99" are converted to years from "1970" to "1999".

• When a conversion between date and time types is performed, if the source type is the DATE type and the destination type is the DATETIME or TIMESTAMP type, the converted value has a time part of '00:00:00'. If the source type is the TIME type and the destination type is the DATETIME or TIMESTAMP type, the converted value has a date part that is the current date of the system, which is affected by the timestamp variable.

• If a date and time value is used in a numeric operation, such as an addition operation, OceanBase Database converts the value to a numeric type. Conversely, if a numeric value is used in a numeric operation, OceanBase Database converts the value to a date and time type.

• In general, if a date or time value is out of the valid value range or is an invalid value, OceanBase Database converts the value to the zero value of the type. For the DATETIME type, the zero value is '0000-00-00 00:00:00'. For the DATE type, the zero value is '0000-00-00'. However, an out-of-range TIME value is converted to a boundary value in the valid value range of the TIME type.

• You can set the SQL Mode variable to control which date values are supported in OceanBase Database. For example, if you add the ALLOW_INVALID_DATES mode to the SQL Mode, the database accepts date values such as '2009-11-31'. In this case, the database only checks whether the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

• OceanBase Database allows you to set the date part to '0000-00-00' for the DATETIME or TIMESTAMP type. In this case, you must set the time part to '00:00:00'.

The following table shows the zero values of all date and time types. You can explicitly use the zero values in the table or use 0 or '0', which is more convenient.