24

I want to offer the user the possibility to introduce dates that are incomplete (for example just the year 2005, or Jan 1998) but also complete dates with year, month, day. Can I accomplish this using a single date field in the DB?

I have tried UPDATE tableA SET mydate = '2001-00-00' and it seems to work (no errors) but I'm not sure if this is correct. Internally I assume a timestamp is used so how can MySQL represent those 0's?

Omar Kohl
  • 1,372
  • 2
  • 15
  • 32
  • What do you get when you query `SELECT mydate FROM tableA` ? – phlogratos Jul 30 '11 at 11:24
  • The correct value i.e. 2001-00-00. My worry is just that I'm exploiting some none standard behaviour that relies on some trick that might some day break or not work in certain circumstances. – Omar Kohl Jul 30 '11 at 11:30

3 Answers3

26

There's a small note in the documentation for the DATE_FORMAT() about MySQL allowing incomplete dates:

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'.

As pointed out by @wonk0, MySQL will sets invalid dates to 0000-00-00 00:00:00 when ALLOW_INVALID_DATES is not set. However, even when ALLOW_INVALID_DATES is not set, inserting a valid year with month and day set to zero does not appear to trigger this behaviour - at least not in my testing (MySQL 5.1.54-1ubuntu4). I have used this feature before without any problem, but I have so far been unable to find any more detailed documentation that describes this behaviour in full.

Date/Time comparisons also appear to work as expected: for example, 2011-01-00 > 2011-00-00 and 2011-00-01 > 2011-00-00 do as you would expect.

UPDATE

See this answer (by another Mike) to a similar question. It points to an extract from The Definitive Guide to MySQL 5:

In older versions of MySQL, the DATE and DATETIME data types did only a limited amount of type checking. Values between 0 and 12 for months, and 0 and 31 for days were generally allowed. However, it is the responsibility of the client program to provide correct data. (For example, 0 is a permissible value for a month or day, in order to provide the possibility of storing incomplete or unknown data.)

Beginning with MySQL 5.0.2, there is a more thorough validation, so that only valid data can be stored. Still allowed are the month and day values 0, as well as the date 0000-00-00.

Community
  • 1
  • 1
Mike
  • 21,301
  • 2
  • 42
  • 65
  • 1
    Is the assumption that a timestamp is used to store the date therefore incorrect? Because I don't see how you can represent 2011-00-00 as a timestamp. – Omar Kohl Jul 30 '11 at 11:49
  • 1
    @Omar Kohl: Yes, I think that assumption is incorrect. A `TIMESTAMP` has a smaller range than a `DATETIME`. A `DATETIME` uses 8 bytes, whereas a `TIMESTAMP` uses 4 bytes. See [Data Type Storage Requirements](http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html#id918335) – Mike Jul 30 '11 at 12:09
  • 1
    Excellent answer, the best I have found searching far and wide. – Timo Huovinen Nov 08 '13 at 19:23
  • 1
    Unfortunately, it appears that on MySQL 5.7+ this behavior is disallowed by default: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date – Code Commander Feb 01 '19 at 19:21
3

Well, if you used a date field then you should expect those 0s. One way you can get rid of them is by using the varchar selection rather. However, it is better to use a date field since thats what is stored there.

If you have to allow users to enter an incomplete date then it has to be a varchar and in that case you can format the input with your script and store in that column

DaMainBoss
  • 2,035
  • 1
  • 19
  • 26
  • That would be my last resort. Of course having things in date format is always nicer for ordering etc. – Omar Kohl Jul 30 '11 at 11:31
  • 1
    yeah so rather than allowing users to format the date, i advice you just allow sql to format the current `datetimestamp` there – DaMainBoss Jul 30 '11 at 11:50
1

Storing parts of dates depends on the sql mode MySQL is running in. Please read http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html, especially about invalid dates in http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_allow_invalid_dates.

wonk0
  • 13,402
  • 1
  • 21
  • 15
  • 1
    "ALLOW_INVALID_DATES: Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31." In this case it doesn't apply because neither month nor day are in that range... (00 both) – Omar Kohl Jul 30 '11 at 11:34
  • `Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31` Why does this allow a month or a day of 0 ? – phlogratos Jul 30 '11 at 11:36