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.
我不能使用日期和函数来实时提取月份，因为表格很大，所以我需要预处理。
2 个解决方案
#1
7
I would go with what @Michael proposes.
我会选择@Michael提出的建议。
Extracting month and year from a date is super fast with EXTRACT or to_char(), there is probably no need for preprocessing.
使用EXTRACT或to_char（）从日期中提取月份和年份非常快，可能不需要预处理。
And a date only occupies 4 byte on disc, doesn't get better than this.
并且一个日期在光盘上只占用4个字节，并没有比这更好。
The alternative are two integer columns with column constraints to safeguard against illegal dates. Occupies 2x 4 bytes.
另一种选择是两个整数列，其中包含列约束以防止非法日期。占用2x 4个字节。
Or even smallint to save RAM and disc storage. Read about and understand data alignment in on-disc storage. In many cases you save nothing with smallint columns. I have written more on that here: Calculating and saving space in PostgreSQL
甚至小巧，以节省RAM和磁盘存储。阅读并理解光盘存储中的数据对齐。在许多情况下，您不使用smallint列保存任何内容。我在这里写了更多：在PostgreSQL中计算和节省空间
Best to go with a date column.
最好使用日期列。
#2
3
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.
可能最明智的是将date_trunc（）用于月份。如果需要，您还可以为date_trunc（）添加一个等于该值的检查约束。
#1
7
I would go with what @Michael proposes.
我会选择@Michael提出的建议。
Extracting month and year from a date is super fast with EXTRACT or to_char(), there is probably no need for preprocessing.
使用EXTRACT或to_char（）从日期中提取月份和年份非常快，可能不需要预处理。
And a date only occupies 4 byte on disc, doesn't get better than this.
并且一个日期在光盘上只占用4个字节，并没有比这更好。
The alternative are two integer columns with column constraints to safeguard against illegal dates. Occupies 2x 4 bytes.
另一种选择是两个整数列，其中包含列约束以防止非法日期。占用2x 4个字节。
Or even smallint to save RAM and disc storage. Read about and understand data alignment in on-disc storage. In many cases you save nothing with smallint columns. I have written more on that here: Calculating and saving space in PostgreSQL
甚至小巧，以节省RAM和磁盘存储。阅读并理解光盘存储中的数据对齐。在许多情况下，您不使用smallint列保存任何内容。我在这里写了更多：在PostgreSQL中计算和节省空间
Best to go with a date column.
最好使用日期列。
#2
3
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.
可能最明智的是将date_trunc（）用于月份。如果需要，您还可以为date_trunc（）添加一个等于该值的检查约束。