Datetime data types

The current version of OceanBase Database supports the DATE, TIME, DATETIME, TIMESTAMP, and YEAR data types for date and time. Each type has 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 in multiple formats that you enter.

• OceanBase Database tries to parse date-only literals in multiple formats that you enter. The date must be in the "year-month-day" format, for example, '98-09-04', but not in the "month-day-year" or "day-month-year" format, for example, '09-04-98' or '04-09-98'. If the date is not in the "year-month-day" format, use the STR_TO_DATE function to convert the date.

• If the year part of a value that you enter is a two-digit number, the century cannot be determined. OceanBase Database converts a one-digit or two-digit year as follows: the years from "70" to "99" are converted to the years from "1970" to "1999", and the years from "00" to "69" are converted to the years from "2000" to "2069".

• When a date and time value is converted between date and time types, if the source type is the DATE type and the target 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 target 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, for example, as an operand of an addition operation, OceanBase Database converts the value into a numeric type. Conversely, if a numeric value is used in a date and time operation, the numeric value is converted into a date and time value.

• Generally, if a value that is out of the valid value range or an invalid value of a date or time type is detected, the database converts the value into 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'. For a TIME value that is out of the valid value range, the database converts the value into the boundary value of the valid value range of the TIME type.

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

• OceanBase Database allows you to set the date part to '0000-00-00'. If the type is DATETIME or TIMESTAMP, you must set the time part to '00:00:00'.

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