Menu
What is the best PostgreSQL data type for year data, e.g., 2006 or 1847. TEXT, SMALLINT, DATE? Ideally, I'd like to be able to query that column with a second year-month-day column (in DATE format).
347
However, storing month and year is easily done in at least three ways: use a date field, but always store into it the first day of the month at midnight; then always custom format the date field for display add integer year and month fields, and populate them by splitting a date
Hey,you can Use year() datatype in MySQL It is available in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Storing a "Year" in MSSQL would ideally depend on what you are doing with it and what the meaning of that "year" would be to your application and database.
Which datatype should I use. 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).
Sql Server will store the datetime data in its entirety (year-month-day hour:min:sec.milliSec) no matter what. When you store your dates, you can make the day the 1st of the month. Just format the dates how you want when you do your queries.
A year is an integer and even supports integer arithmetic in a meaningful way so text makes no sense. You don't have a month or day so date is right out the window. The fine manual has this to say about smallint:
The
smallinttype is generally only used if disk space is at a premium.
That leaves integer as a natural choice. If you're planning to combine this value with dates and timestamps, then interval might make sense as the date and time functions and operators have a good understanding of intervals.
So it depends on what you're planning on doing with this "year" and what it really is. Sounds like a simple integer is appropriate but you might have to muck around a bit to combine it with your "second year-month-day column" (a date column presumably) depending on how they need to work together.
192
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
