Best pratice to store dates group by months-year key par value

Question

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I should use for storing month - year value. Here is what I have considered.

1. Two Integer columns one for year other for month (clear to understand and strong to manipulate (anybody who uses this table can easily use ranges and orders) but I think that would take more space than another solution.
2. Use a Date field, and always store one date for entire month. (Hard to explain to users how it works and how to create WHERE clauses)
3. One varchar(7) column and put there stings like 2012-02,2013-01. For me this is easy to understand but hard to manipulate.

Whick I should pick? or is there other solution. Can someone suggest according to performance which way is going to be good solution, Because most of queries is going to using data range in where solution and my new table will have around 2-5 million records.

Answer 1

I'd use a date field with the 1st of a month and a CHECK constraint to ensure it stays day 1.

This keeps it in the native date/time format (which is your observation about option 3)

Option 1 would requires less storage, but complicates comparisons. And 5 million rows isn't much: you use less storage but add code and query complexity

Answer 2

I think you should choose one (or more) options depending on how the data needs to be retrieved.

For 5 million records, space should really not be your primary concern. For each of your options here are the pros and cons for retrieval:

(1) Two Integer Columns: This is a great approach to use if you need to compare months from different years. Extracting by month and sorting by year will be much quicker if you index year and month separately. Option 1 is best if this is an important or frequent mode of using the data. On the other hand, this mode is terrible at extracting ranges other than years and months. For example, it is not good for ranges that cross years. The WHERE clause could get more complicated than you'd ideally like it to be when date ranges cross calendar year boundaries. (Think from November 2011 to February 2012.)

(2) Date Field: You and gbn have both identified good points about this format. It is also good for sorting chronologically and for extracting ranges of months. It happens to be the most compact representation (only 3 bytes). It isn't good at all for comparing like months in various years and it isn't idea for displaying in screens and reports.

(3) char(7) YYYY-MM Field: Note that if you are really concerned about space (and you shouldn't be in your case) then you can use char instead of varchar since every item will be of a known length. Using YYYY-MM is good for sorting and filtering ranges. It isn't as good as (2) for space, but it is better for display and simplicity of the WHERE clause - unless you need to extract like months across years.

---

You may have noticed that there is a difference between (1) and (2)/(3) when it comes to the nature of the extraction that you need to do. If you need to do both month/year over year and month range extractions then none of these options are perfect. If that is the case, I'd suggest you consider using a combination of (1) and either (2) or (3) - I'd pick (3) myself, since I'd value ease of display/use over storage space. If you do use a combination, make one or the other a computed column(s) and index it for efficient retrieval.

Answer 3

Consider modelling as a period with two date values -- start date and end date -- using closed-open representation (the 'open' indicates that the end date does not actually occur in the period).

For example, the current month (March 2012) would be modelled using the row

(start_date, end date) VALUES ('2012-03-01T00:00:00', '2012-04-01T00:00:00')

Answer 4

If you also have the full date in the table and use the reduced one for aggregation only, then make a function that creates a normalized, ideally sensibly distributed value (e.g. "months since January 1970") from the date, and create an index on the result of that function to speed up the aggregation process.