CODEWITHSUNDEEP
mysqldatabasedatabase-design
westoque
westoque

Reputation: 417

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

I do realize that it is better for a column to be an Integer if one has to perform mathematical calculations on it.

I probably have to perform mathematical calculations on the "year" column but minimally. So would it be better to store it as a String or Integer?

Thanks.

Upvotes: 6

Views: 12649

Answers (3)

Arth
Arth

Reputation: 13110

You could go crazy and save it as a YEAR!

This limits you to 1901-2155

You can do things like

WHERE year < CURDATE()

without worries then.

Upvotes: 1

Russell Steen
Russell Steen

Reputation: 6612

Save it as an integer.

While there may be some application where you are reading and serving this data so frequently that the int->string conversion is a problem... that is going to be an edge case.

On the other side

  • Integers provide smaller options than strings in data storage (such as TINYINT)
  • You avoid conversions due to math
  • It's going to confuse/annoy/frustrate all the developers that come after you when they query a data type that is naturally a number and get a string.

Upvotes: 9

GGio
GGio

Reputation: 7653

If you are not expecting your YEAR variable to ever contain non-digit values then yes you should store it as a number.

I would not store it as INT since I doubt year will reach the limit that INT has to offer. I would save it as SMALLINT or even TINYINT either should be unsigned.

SMALLINT UNSIGNED gives you max value of 65535, unless you are storing years that exceed the year 65535 this should suffice.

Upvotes: 3

Related Questions