Date and time literals can be represented as either string text or numeric values.
The differences between string and numeric data are as follows:
String text formats can use full delimiters, for example,
'2015-07-21 12:34:56.789'; or no delimiter at all, for example,'20150721'.In numeric formats, only the decimal point
.is allowed as the separator between seconds and microseconds, for example:20150721123456.789.
When using delimiters, it is recommended to use only common delimiters such as -, /, and : except when a decimal point . is required between the second and microsecond parts.
DATE values
OceanBase AI recognizes DATE values in the following formats:
'YYYY-MM-DD'or'YY-MM-DD'. Any punctuation character can be used as a separator between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.The
'YYYYMMDD'or'YYMMDD'format, provided the string represents a valid date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071532' is invalid (it contains meaningless month and day) and returnsNULL.YYYYMMDDorYYMMDDformat, provided the number is a valid date value. For example, 19830905 and 830905 are interpreted as "1983-09-05".Notice
In the
STRICT_TRANS_TABLESandNO_ZERO_IN_DATESQL modes, an error is returned if a date value contains a zero in its month or day field, except for '0000-00-00'.
DATETIME and TIMESTAMP values
OceanBase AI Database recognizes DATETIME and TIMESTAMP values in the following formats:
The format is
'YYYY-MM-DD hh:mm:ss'or'YY-MM-DD hh:mm:ss'. Any punctuation character can be used as a separator between the date and time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45", and '2012@12@31 11^30^45' are equivalent.Note that the only separator that can be recognized between the date and time parts and the fractional second part is the decimal point. The date and time parts can be separated by
Tinstead of a space. For example, '2012-12-31 11:30:45' is equivalent to '2012-12-31T11:30:45'.The
'YYYYMMDDhhmmss'or'YYMMDDhhmmss'format. The string must represent a valid date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is invalid (its minute component is meaningless) and returnsNULL.YYYYMMDDhhmmssorYYMMDDhhmmssformat. The string must represent a valid date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
DATETIME or TIMESTAMP values can include a fractional second value, with a precision of up to microseconds (6 digits). The fractional part must always be separated from the rest of the time by a decimal point; other decimal-second separators are not recognized.
OceanBase AI Database uses the following rules to interpret dates that contain two-digit year values:
Year values in the 70-99 range are changed to 1970-1999.
Year values in the range of 00-69 are changed to 2000-2069.
For month or day values less than 10, two digits are not required. For example, '2015-6-9' is the same as '2015-06-09'. Similarly, for hour, minute, or second values less than 10, two digits are not required. For example, '2015-10-30 1:2:3' is the same as '2015-10-30 01:02:03'.
Additionally, OceanBase AI Database will also select a specific format based on the length of the number:
A six-digit number generally follows the format
YYMMDD.An 8-digit number is generally in the format
YYYYMMDD.The general format for a 12-digit number is
YYMMDDHHMMSS.The general format for a 14-digit number is
YYYYMMDDHHMMSS.
TIME values
OceanBase AI Database recognizes TIME values in the following formats:
'D hh:mm:ss', or'hh:mm:ss','hh:mm','D hh:mm','D hh', and'ss'. Here,Drepresents the number of days, with valid values being integers from 0 to 34.The
hhmmssformat, which has no separator. The number must represent a valid time value. For example, '101112' is interpreted as '10:11:12', but '109712' is invalid (it contains an meaningless minute component) and is converted to '00:00:00'.hhmmssformat, orss,mmss, orhhmmss. The number must be a valid time value. For example, 101112 is interpreted as '10:11:12'.
The time formats 'D hh:mm:ss.fraction', 'hh:mm:ss.fraction', 'hhmmss.fraction' and hhmmss.fraction recognize a fractional second value at the end, with precision up to microseconds (6 digits). The fractional part must always be separated from the rest of the time by a decimal point; other fractional second separators are not recognized.