Date and time literals can be represented as strings or numbers.
The differences between string and numeric representations are as follows:
String literals can use full separators, such as
'2015-07-21 12:34:56.789', or omit separators, such as'20150721'.Numeric literals can only use the decimal point
.as a separator for seconds and microseconds. For example:20150721123456.789.
When using separators, only the decimal point . is allowed between seconds and microseconds. For other cases, it is recommended to use common separators such as -, /, and :.
DATE values
OceanBase Database recognizes the following DATE values:
'YYYY-MM-DD'or'YY-MM-DD'format. 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.'YYYYMMDD'or'YYMMDD'format, provided that the string represents a valid date value. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071532' is invalid (it contains an invalid month and day) and returnsNULL.YYYYMMDDorYYMMDDformat, provided that the number represents 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, if the month or day in a date contains a zero, an error will be returned, except for '0000-00-00'.
DATETIME and TIMESTAMP values
OceanBase Database recognizes the following DATETIME and TIMESTAMP values:
'YYYY-MM-DD hh:mm:ss'or'YY-MM-DD hh:mm:ss'format. Any punctuation character can be used as a separator between date parts or 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 recognized between the date and time parts and the fractional seconds 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'.'YYYYMMDDhhmmss'or'YYMMDDhhmmss'format. Provided that the string represents a valid date value. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is invalid (it contains an invalid minute) and returnsNULL.YYYYMMDDhhmmssorYYMMDDhhmmssformat. Provided that the number represents a valid date value. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
DATETIME or TIMESTAMP values can include a fractional seconds part at the end, with a precision of up to six digits (microseconds). The fractional part should always be separated from the rest of the time by a decimal point; other fractional seconds separators are not recognized.
OceanBase Database interprets date values with two-digit years based on the following rules:
Years in the range 70-99 are interpreted as 1970-1999.
Years in the range 00-69 are interpreted as 2000-2069.
For months or days less than 10, no leading zeros are required. For example, '2015-6-9' is equivalent to '2015-06-09'. Similarly, for hours, minutes, or seconds less than 10, no leading zeros are required. For example, '2015-10-30 1:2:3' is equivalent to '2015-10-30 01:02:03'.
Additionally, OceanBase Database selects the appropriate format based on the length of the number:
6-digit numbers are interpreted as
YYMMDD.8-digit numbers are interpreted as
YYYYMMDD.12-digit numbers are interpreted as
YYMMDDHHMMSS.14-digit numbers are interpreted as
YYYYMMDDHHMMSS.
TIME values
OceanBase Database recognizes the following TIME values:
'D hh:mm:ss'format, or'hh:mm:ss','hh:mm','D hh:mm','D hh', and'ss'. Here,Drepresents the number of days, which can be an integer from 0 to 34.'hhmmss'format without separators. Provided that the number represents a valid time value. For example, '101112' is interpreted as '10:11:12', but '109712' is invalid (it contains an invalid minute) and is interpreted as '00:00:00'.hhmmssformat, orss,mmss, orhhmmss. Provided that the number represents a valid time value. For example, 101112 is interpreted as '10:11:12'.
In the time formats 'D hh:mm:ss.fraction', 'hh:mm:ss.fraction', 'hhmmss.fraction', and hhmmss.fraction, the fractional seconds part at the end is recognized, with a precision of up to six digits (microseconds). The fractional part should always be separated from the rest of the time by a decimal point; other fractional seconds separators are not recognized.