MySQL datatype to store month and year only

Question

I'm writing a PHP application that will store STUDENT data in a MySQL relational database. I'm trying to find the best way / datatype to store a month and year together without the day.

I don't know whether I should just store it as a DATE and use PHP someway to just store the day as the 1-st or use a different datatype that I'm not currently familiar with. Ideally, I do not want to store a day, because the day will not always be the same and would require changing PHP source code if the day changed in the future.

Just for more background info, I'm storing a STUDENT's INTENT_TO_GRAD. The client seems to only want this information as a reference or a visual for a report as opposed to using it for data manipulation. In other words, the only functional requirement for this data is to be displayed in a report.

Have you considered storing Month and Year in separate fields as SMALLINT? — Marcus Adams, Feb 03 '12 at 19:43

score 77 · Accepted Answer · edited Mar 12 '14 at 19:25

77

Why bother? Just store it as a complete date (perhaps always using the first as the day) and use the database functions MONTH() and YEAR() if you only need part of it. This makes using that field much easier as you can still do range queries, etc.

edited Mar 12 '14 at 19:25

Michel Ayres

5,891
10
63
97

answered Feb 03 '12 at 19:48

Brian Roach

76,169
12
136
161

2

I'd agree with just storing it as a date. It's very possible that later the client will want to enter the student's ACTUAL graduation date. – Adrian J. Moreno Feb 03 '12 at 19:53
4

You can do range queries if you store them separately too, and faster than using MONTH() and YEAR() functions. If you do store them together definitely use 1st day, as there always is one of those, and you can just use dates like `BETWEEN '2012-04-01' AND '2012-12-01'` to search a range. – Marcus Adams Feb 03 '12 at 20:01
@MarcusAdams, I agree with the performance part, I don't understand the "If you do store them together definitely use 1st day, as there always is one of those". I don't know many students who graduated on June 31st. – Kyle Macey Feb 03 '12 at 23:53
I guess if I stored it as a complete date, using the 1st as the day, I could choose to just display the first 7 characters using SUBSTR() or a LEFT() to only show the YYYY-MM part by cutting out the -DD. – Daniel Wilhoit Feb 04 '12 at 05:23
8

+1 for using `DATE` but instead use `EXTRACT(YEAR_MONTH FROM mydate)` for comparisons and `DATE_FORMAT(mydate, '%Y-%M')` for display. Don't forget the other handy [date functions](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html) – KCD Jun 05 '12 at 21:29
but what to do if some of the dates are known, yet some arent? for example, for most entries the dates are known (e.g. `2018-06-13`, `2016-07-02`, etc), but for some entries the days in the dates are unknown (e.g. `2018-06-??`, `2016-07-??`, etc). is there still no way to deal with this in MySQL nowadays? – oldboy Feb 16 '19 at 21:21
@BugWhisperer, just in case or anyone else still cares about this: You can actually do something like `INSERT INTO testtbl (name, dob) VALUES ('Abiodun', '1998-12-00');` which essentially takes care of the scenario you painted. – Ifedi Okonkwo Sep 20 '19 at 18:47
@IfediOkonkwo thanks!! i ended up just using NULL values – oldboy Sep 21 '19 at 19:02
I'm not sure why this is the selected answer, as it doesn't actually address the question as written. OP, please consider accepting the answer from Timo instead... – jetsetter Feb 15 '21 at 00:32

score 73 · Answer 2 · edited May 23 '17 at 12:34

73

It says in the MySQL manual that you can store partial dates

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

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'.

This means that to store the year and month only, you can use a DATE column and put 00 instead of the day. e.g 2013-12-00.

Related: Store incomplete date in MySQL date field

edited May 23 '17 at 12:34

Community

1
1

answered Dec 18 '13 at 13:25

Timo Huovinen

53,325
33
152
143

7

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:24
@CodeCommander good catch, fortunately you can modify the sql_mode `SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';` as you mention in your link – Timo Huovinen Feb 02 '19 at 17:14

score 7 · Answer 3 · answered Feb 03 '12 at 19:46

7

Consider how you are going to use the data. If there are any reports you have to create, which way would allow you to retrieve and work with the data more easily?

And you don't have to use a date type field. You could just have a Year field and a Month field that are both integers. Then when you actually need to do any kind of expression with it requiring a date it's easy enough to put them together and cast to a date.

And storing as a date with the day number as 1 and just ignoring it is perfectly okay and fairly normal too. In the end this isn't a decision that's going to matter a whole lot (relatively speaking) so I would just choose the one you like best and get it done.

answered Feb 03 '12 at 19:46

Brandon Moore

8,590
15
65
120

2

You can store year and month in separate columns as integers. But MySQL makes it mildly more painful, because it doesn't support `CHECK (month between 1 and 12)`; you have to add a table and a foreign key constraint to guarantee actual months. – Mike Sherrill 'Cat Recall' Feb 04 '12 at 00:49
@Catcall Eh, I wouldn't have added the check anyway so not an issue for me :) – Brandon Moore Feb 04 '12 at 04:08
I may be wrong, but couldn't you just use CHECK (month in ('1','2','3',...))... or just have the app check that – Daniel Wilhoit Feb 04 '12 at 05:14
1

MySQL doesn't support CHECK constraints. – Mike Sherrill 'Cat Recall' Feb 04 '12 at 05:25
1

Whether you are using MySql or not, the application really should be enforcing this constraint before it even gets to the database. – Brandon Moore Jan 07 '13 at 10:22

score 5 · Answer 4 · answered May 22 '19 at 11:21

Another solution is to build generated columns from your DATETIME/DATE source.

ALTER TABLE stats 
ADD COLUMN year SMALLINT GENERATED ALWAYS AS (YEAR(stat_date)) NOT NULL,
ADD COLUMN month smallint GENERATED ALWAYS AS (MONTH(stat_date)) NOT NULL;

Background

I had a similar problem. After reading this thread, I decided to store incomplete dates (with zeros). It worked on older versions of MySQL, but newer versions produced "Incorrect date" error. As mentioned before, you can turn the error into warning using the NO_ZERO_IN_DATE setting. But the setting itself is deprecated. Hence, in the future, it would only be possible to support zeros in dates by disabling the strict mode, thus, silencing other types of errors.

Because NO_ZERO_IN_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

My requirement was to build a monthly view of a table. I had to add an index on month. So, computing moth on-the-fly with YEAR() and MONTH() was not an option.

Generated columns served their purpose. The year and month columns weren't part of the primary index, and I could even save space thanks to the VIRTUAL (not STORED) generated columns.

Links

score 3 · Answer 5 · answered Jul 26 '15 at 11:54

SELECT * FROM Users
WHERE 
    MONTH(DateTime) = '07'
AND 
    YEAR(DateTime) = '2015'
AND 
    DAY(DateTime) = '26'

you can filter like above but still if you want to store year/month and day as separate, its useless until you apply indexing and its very big data.

score 3 · Answer 6 · answered Feb 03 '12 at 19:44

3

I would store the two as two separate columns as integers. It would make validation cake and allow quick and easy sorting and grouping possibilities.

answered Feb 03 '12 at 19:44

Kyle Macey

8,074
2
38
78

3

terrible solution. what if you want to get data from 3 months back? you'll need do some quirk like (Year x 12 + Month - 1) < (current_year x 12 + current_month -1 - 3) so forget about indexing – Peter Apr 07 '16 at 12:58
2

@Peter The original question stated that they only wanted to use the data for display. I don't see how your math applies. – Kyle Macey Apr 14 '16 at 02:49
@Peter if i store the `year`, `month`, and `day` as separate columns, id still be able to sort and filter them on the back and front end. id simply have to retrieve all the values and combine them or create a date object from them – oldboy Feb 16 '19 at 21:25
1

@Anthony it's 2019-02-17. please build a query for records from last 24 days. nightmare. "id simply have to retrieve all the values" or you can build query `select .. from ... where date > '...'` done, that's main purpose of db – Peter Feb 16 '19 at 23:24
@Peter but i would never need to select the last 24 days in my application :/ – oldboy Feb 16 '19 at 23:27
@Anthony 24 days, 3 days, 10 days, 100 days, 3 months, 1 week, 3 weeks, anything really... – Peter Feb 18 '19 at 13:22
Better create a view for that use case then. Because then it is required to work with the data in form of actual dates. But if it is only for display I would also argue that storing a day is incorrect and leads to potentially misinterpreted data. It has different semantics to not know the day or defaulting it to e.g. the 1st of the month. – Stuck Jul 08 '20 at 12:23

score 1 · Answer 7 · answered Aug 04 '20 at 16:33

As of 5.7 one could make use of generated columns, which lets you enforce year-month uniqueness while also using date functions on another field without breaking data integrity by duplicating:

CREATE TABLE `year_month` (
  `Year` int(11) NOT NULL,
  `Month` enum(
      'January',
      'February',
      'March',
      'April',
      'May',
      'June',
      'July',
      'August',
      'September',
      'October',
      'November',
      'December'
      ) NOT NULL,
  `GENERATED_YearMonth` date
      GENERATED ALWAYS AS
          ((makedate(`Year`,1) + interval (`Month` - 1) month))
      STORED,
  PRIMARY KEY (`Year`,`Month`),
  KEY `year_month_GENERATED_YearMonth_index` (`GENERATED_YearMonth`)
)
;

Ritz · Answer 8 · 2017-01-05T08:30:32.743

You can use VARCHAR datatype to store month and year only.

For those who use datepicker :-

1)Set VARCHAR datatype in mysql to store month and year.

2)If you are using jquery validation and have jquery plugin date picker then you have to do below code.

For ex:-

<script>

$(document).ready(function (){

$('#monthyear').datepicker({
  autoclose:true,
  format:'mm-yy'
});

//Your form validation code here//

});
</script>

MySQL datatype to store month and year only

8 Answers8

Linked

Related