Menu
I will be storing a year in a MySQL table: Is it better to store this as a smallint or varchar? I figure that since it's not a full date, that the date format shouldn't be an answer but I'll include that as well.
Smallint? varchar(4)? date? something else?
Examples:
2008
1992
2053
292
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' . As 4-digit numbers in the range 1901 to 2155 .
There is nothing stopping you putting non-date data in the VARCHAR column in the database. The VARCHAR version is culture specific. You can't easily sort the dates. It is difficult to change the format if you want to later.
The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
I would use the YEAR(4) column type... but only if the years expected are within the range 1901 and 2155... otherwise, see Gambrinus's answer.
82
I'd go for small-int - as far as I know - varchar would take more space as well as date. second option would be the date.
45
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
