MySQL datatype to store month and year only

Mysql Problem Overview

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.

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

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.

Solution 3 - Mysql

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.

Solution 4 - Mysql

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

Solution 5 - Mysql

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.

Solution 6 - Mysql

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.

Solution 7 - Mysql

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`)
)
;

Solution 8 - Mysql

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>

Content Type	Original Author	Original Content on Stackoverflow
Question	Daniel Wilhoit	View Question on Stackoverflow
Solution 1 - Mysql	Brian Roach	View Answer on Stackoverflow
Solution 2 - Mysql	Timo Huovinen	View Answer on Stackoverflow
Solution 3 - Mysql	Brandon Moore	View Answer on Stackoverflow
Solution 4 - Mysql	shapiy	View Answer on Stackoverflow
Solution 5 - Mysql	Kyle Macey	View Answer on Stackoverflow
Solution 6 - Mysql	Furqan Freed	View Answer on Stackoverflow
Solution 7 - Mysql	Nae	View Answer on Stackoverflow
Solution 8 - Mysql	Ritz	View Answer on Stackoverflow