Menu
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.
310
If you need to store a year in the database, you would either want to use an Integer datatype (if you are dead set on only storing the year) or a DateTime datatype (which would involve storing a date that basically is 1/1/1990 00:00:00 in format). – KM. @KM.
To get the year and the month columns, use the EXTRACT(part FROM date) function. In this solution, the part argument is replaced by YEAR and MONTH to get the year and the month separately, each in its own column. You can learn more about EXTRACT() in the official MySQL documentation.
MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155 , and 0000 . YEAR accepts input values in a variety of formats: As 4-digit strings in the range '1901' to '2155' .
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.
85
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.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
