Skip to main content

Date and time values

Date and time literals can be in string or numeric form.

The difference between string and numeric forms is as follows:

  • String literals can use full separators, such as '2015-07-21 12:34:56.789' or no separators, such as '20150721'.

  • Numeric literals can only use the decimal point . as the separator for seconds and microseconds. For example: 20150721123456.789.

When using separators, only the decimal point . can be used between seconds and microseconds. Other separators are recommended to be used only for common cases.

DATE values​

seekdb 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. For example, '20070523' and '070523' are interpreted as '2007-05-23'. However, '071532' is invalid (it contains invalid month and day values) and returns NULL.

  • YYYYMMDD or YYMMDD format, provided that the number represents a valid date. For example, 19830905 and 830905 are interpreted as "1983-09-05".

    tip

    In STRICT_TRANS_TABLES and NO_ZERO_IN_DATE SQL modes, a value with 0 in the month or day part of a date will result in an error, except for the '0000-00-00' value.

DATETIME and TIMESTAMP values​

seekdb 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 T instead 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. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28'. However, '071122129015' is invalid (it contains invalid minutes) and returns NULL.

  • YYYYMMDDhhmmss or YYMMDDhhmmss format. Provided that the number represents 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 seconds part at the end, with a precision of up to microseconds (6 digits). The fractional part must always be separated from the rest of the time using a decimal point; other separators for fractional seconds are not recognized.

seekdb interprets date values with two-digit years using 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 values less than 10 for the month or day, you do not need to specify two digits. For example, '2015-6-9' is equivalent to '2015-06-09'. Similarly, for values less than 10 for the hour, minute, or second, you do not need to specify two digits. For example, '2015-10-30 1:2:3' is equivalent to '2015-10-30 01:02:03'.

Additionally, seekdb 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​

seekdb recognizes the following TIME values:

  • 'D hh:mm:ss' format, or 'hh:mm:ss', 'hh:mm', 'D hh:mm', 'D hh', and 'ss'. Here, D represents 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. For example, '101112' is interpreted as '10:11:12'. However, '109712' is invalid (it contains invalid minutes) and is interpreted as '00:00:00'.

  • hhmmss format, or ss, mmss, or hhmmss. Provided that the number represents a valid time. 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, seekdb recognizes a fractional seconds part at the end, with a precision of up to microseconds (6 digits). The fractional part must always be separated from the rest of the time using a decimal point; other separators for fractional seconds are not recognized.

Contents

Edit this page