Clever Geek Handbook

Is it better to define a "year" column of type Integer or String?

I understand that it is better to have a whole column if you need to perform mathematical calculations on it.

I probably have to do some math on the "year" column, but minimal. So would it be better to store it as String or Integer?

Thank.

+3


source to share


3 answers


Save it as an integer.

While there might be some application where you read and serve this data a lot that int-> string conversion is a problem ... this would be an edge case.



On the other hand

  • Integers are less powerful than strings in a data store (such as TINYINT).
  • You avoid conversions because of math
  • This will confuse / annoy / frustrate all developers who come after you when they ask for a datatype, which is naturally a number and receives a string.
+3


source


If you don't expect your variable YEAR

to ever contain non-numeric values, then yes, you should store it as a number.

I would not save it as INT

, as the year is unlikely to reach the limit it has to offer INT

. I would keep it as SMALLINT

or even TINYINT

should be unsigned

.



SMALLINT UNSIGNED

gives the maximum value 65535

, if you are not storing years longer than a year 65535

this should be sufficient.

+3


source


You can go crazy and save it like YEAR

!

This limits you to 1901-2155.

If it is too restrictive, I prefer CHAR(4)

to INT

; MySQL comparison DATETIME

is done as a string.

You can do things like

WHERE year < CURDATE()

no problem.

0


source






More articles:


All Articles