The Ultimate Guide to High Availability with MariaDB

Date and time literals

You are viewing an old version of this article. View the current version here.

Standard syntaxes

MariaDB starting with 10.0

MariaDB 10.0 supports the SQL standard and ODBC syntaxes for DATE, TIME and TIMESTAMP literals.

SQL standard syntax:

  • DATE 'string'
  • TIME 'string'
  • TIMESTAMP 'string'

ODBC syntax:

  • {d 'string'}
  • {t 'string'}
  • {ts 'string'}

The timestamp literals are treated as DATETIME literals, because in MariaDB the reange of DATETIME is closer to the TIMESTAMP range in the SQL standard.

string is a string in a proper format, as explained below.

DATE literals

A DATE string is a string in one of the following formats: 'YYYY-MM-DD' or 'YY-MM-DD'. Note that any punctuation character can be used as delimiter. All delimiters must consist of 1 character. Different delimiters can be used in the same string. Delimiters are optional (but if one delimiter is used, all delimiters must be used).

A DATE literal can also be an integer, in one of the following formats: YYYYMMDD or YYMMDD.

All the following DATE literals are valid, and they all represent the same value:

'19940101'
'940101'
'1994-01-01'
'94/01/01'
'1994-01/01'
'94:01!01'
19940101
940101

DATETIME literals

A DATETIME string is a string in one of the following formats: 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'. Note that any punctuation character can be used as delimiter for the date part and for the time part. All delimiters must consist of 1 character. Different delimiters can be used in the same string. The hours, minutes and seconds parts can consist of one character. For this reason, delimiters are mandatory for DATETIME literals.

The delimiter between the date part and the time part can be a T or any sequence of space characters (including tabs, new lines and carriage returns).

A DATETIME literal can also be an integer, in one of the following formats: YYYYMMDDHHMMSS or YYMMDDHHMMSS. In this case, all the time subparts must consist of 2 digits.

All the following DATE literals are valid, and they all represent the same value:

'1994-01-01T12:30:03'
'1994/01/01\n\t 12+30+03'
'1994/01\\01\n\t 12+30-03'
'1994-01-01 12:30:3'

TIME literals

2-digits years

The year part in DATE and DATETIME literals is determined as follows:

  • 70 - 99 = 1970 - 1999
  • 00 - 69 = 2000 - 2069

Microseconds

DATETIME and TIME literals can have an optional microseconds part. For both string and numeric forms, it is expressed as a decimal part. Up to 6 decimal digits are allowed. Examples:

'12:30:00.123456'
123000.123456

See Microseconds in MariaDB for details.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.