CODEWITHSUNDEEP
mysqldatetime
Lieuwe
Lieuwe

Reputation: 1840

MySQL and datetime

If I have a table with a DATETIME column I can insert dates that have a format like:

2015-03-25 10:10:10
2015-03-25 10:10
2015-03-25 10
2015-03-25

It will fill in the remainder with zeros. I can't however use

2015-03 
2015

As it will give an 'Incorrect datetime value' error. It is however possible to use these last two in a SELECT like [..] WHERE timestamp < '2015-03' ..

Is there a way that MySQL will fill in the remainder of datetimes with 01-01 for the month and day if omitted in datetimes or do I have to do that manually myself?

I.e. I would like to use '2015-03' in an INSERT statement, or do something like SELECT DATE_FORMAT('2015-03', '%Y%m%dT%H%i%S')

Upvotes: 3

Views: 4955

Answers (2)

eggyal
eggyal

Reputation: 125995

As stated in Date and Time Literals:

MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

  • As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter 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.

    The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

    The date and time parts can be separated by T rather than a space. For example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

  • As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

Notably, MySQL does not support the incomplete formats that you wish to use.

That MySQL happens to accept some of the incomplete formats you've tried (apparently by padding with zeroes) is undocumented behaviour, quite possibly unintended by the developers. It cannot (and should not) be relied upon, not least because edge cases could exist under which the behaviour breaks; or because the behaviour could be changed without warning in a future release.

If it's absolutely necessary to provide such incomplete temporal literals to MySQL (which it shouldn't be, as your data access layer ought to be aware of the type of values it is handling and provide them to MySQL in a supported format), you can use its STR_TO_DATE() function to parse them accordingly:

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0:

mysql> SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'

Range checking on the parts of date values is as described in Section 11.3.1, “The DATE, DATETIME, and TIMESTAMP Types”. This means, for example, that “zero” dates or dates with part values of 0 are permitted unless the SQL mode is set to disallow such values.

So, for example, you might use:

STR_TO_DATE('2015-03', '%Y-%m');

Upvotes: 5

M3ghana
M3ghana

Reputation: 1281

Try unix_timestamp()

SELECT like [..] WHERE unix_timestamp(timestamp) < '2015-03'

Upvotes: 0

Related Questions