11

Is there a standard way to store year and month in a database ? I need to make some reports based on months and years.

I can't use dates and functions to extract months in real time because the tables are huge, so I need preprocessing.

johnlemon
  • 20,761
  • 42
  • 119
  • 178

2 Answers2

16

I would go with what @Michael proposes.

Extracting month and year from a date is super fast with EXTRACT or to_char(), there is probably no need for pre-processing.

A date only occupies 4 bytes on disk, hardly gets better than this.

A possible alternative could be 2 integer columns with column constraints to safeguard against illegal dates. Occupies 2 x 4 bytes.

Or even 2 smallint to save RAM and disk storage. Read about and understand data alignment in storage. In many cases you save nothing with smallint columns. See:

Best to go with a date column.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Not to mention that date arithmetic is simpler. Good luck finding all records between 2016, 9 and 2018, 3 using separate year+month columns. – Salman A Jul 28 '21 at 10:42
  • 1
    @SalmanA: Postgres fully supports ROW comparison: `WHERE (year, month) BETWEEN (2016, 9) AND (2018, 3)`. See: https://stackoverflow.com/a/36465961/939860, https://stackoverflow.com/a/32982895/939860. Still, `date` is the way to go for many reasons. – Erwin Brandstetter Jul 28 '21 at 10:55
  • that was just an example. Adding date interval, checking for date overlaps are other examples. – Salman A Jul 28 '21 at 11:05
8

Probably the most sensible is to use date_trunc() to month. You may also add a check constraint for date_trunc() being equal to the value if you want to.

Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173