Datetime data types

The current version of OceanBase Database supports several datetime-related data types, including DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each type has a specific valid range for its values.

When using datetime data types, consider the following points:

• OceanBase Database generates datetime types in standard formats but attempts to parse user input values in multiple formats.

• Although OceanBase Database tries to parse user input literals in various formats, the date portion must follow the “year-month-day” order, for example, ‘98-09-04’, and not in the “month-day-year” or “day-month-year” order, such as ‘09-04-98’ or ‘04-09-98’. If you need to convert a string that does not follow the “year-month-day” order, you can use the STR_TO_DATE function.

• When the year input consists of only two digits, it can be ambiguous because the century is not specified. OceanBase Database applies the following rule: years between “70-99” are converted to “1970-1999”, and years between “00-69” are converted to “2000-2069”.

• During conversions between different datetime types, if the source type is DATE and the destination type is DATETIME or TIMESTAMP, the converted time portion will be ‘00:00:00’. If the source type is TIME and the destination type is DATETIME or TIMESTAMP, the converted date portion will be the current system date, which is influenced by the system variable timestamp.

• In numeric scenarios, such as using datetime-related types in addition operations, OceanBase Database will convert them to numeric types, and vice versa.

• In general, when a value exceeds the valid range or is an invalid value for a date or time type, OceanBase Database converts it to the zero value for that type. For example, for the DATETIME type, the conversion result is ‘0000-00-00 00:00:00’, and for the DATE type, the conversion result is ‘0000-00-00’. An exception is that time values exceeding the range will be converted to the boundary values of the valid range for the TIME type.

• By setting the SQL mode, you can determine which date values OceanBase Database supports. For example, adding ALLOW_INVALID_DATES to the SQL mode allows the database to accept date values like ‘2009-11-31’, where the database only checks if the month is between 1 and 12, and the day is between 1 and 31. Removing NO_ZERO_IN_DATE from the SQL mode allows the database to accept date values like ‘2020-00-01’ and ‘2020-01-00’, but using functions like DATE_SUB and DATE_ADD may not yield accurate results.

• 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 datetime 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.