Storing just the year in the DB

Rails
1

Hey,

This may be a simple question, but I just wanted to make sure I was
doing it right.

I need to store just the year in my database - i.e. vacation entitlement
year = 2008

Should I store it using ‘date’ and leave the month and day as ‘0’

2008-00-00

or should i store it as an integer?

Many Thanks

2

Well actualy it depends a lot on what you wanna do with this year
data…could you precise a little what is your goal?

On 20 feb, 17:02, Scott H. [email protected]

3

CiriusMex wrote:

Well actualy it depends a lot on what you wanna do with this year
data…could you precise a little what is your goal?

On 20 feb, 17:02, Scott H. [email protected]

I am building a little application to manage our company holiday
entitlement.

When adding a new user, the form will allow the admin user to enter the
holiday entitlement for next couple of years:

e.g.

Name: John S.

Entitlement for year starting 2008: 24 [days]
Entitlement for year starting 2009: 25 [days]

So, in theory, my table will look like:

id
user_id
year
entitlement

4

CiriusMex wrote:

Ok, so the year will just be stored as information (I mean you’ll just
use it to display the information on a page) no?
If it’s like that you store it as a varchar or integer I think. But if
you will need to process the year in other functions well it should be
better to store it as a year.
When creating a new line on your database you can use the Date.today
function to get the current date, after the function strftime will
help you to display only the year:
@year = <your_object>.year.strftime(“%Y”)

displaying @year in your html.erb page you’ll only see the year from
the date, just as you want to.

On 20 feb, 17:32, Scott H. [email protected]

Thank you for the reply.

I am going to use the year to calculate other things.

So will it be ok to leave the the day and the month as ‘0’ in the ‘date’
format?

2008-00-00

5

Ok, so the year will just be stored as information (I mean you’ll just
use it to display the information on a page) no?
If it’s like that you store it as a varchar or integer I think. But if
you will need to process the year in other functions well it should be
better to store it as a year.
When creating a new line on your database you can use the Date.today
function to get the current date, after the function strftime will
help you to display only the year:
@year = <your_object>.year.strftime(“%Y”)

displaying @year in your html.erb page you’ll only see the year from
the date, just as you want to.

On 20 feb, 17:32, Scott H. [email protected]

6

function to get the current date, after the function strftime will
I am going to use the year to calculate other things.

So will it be ok to leave the the day and the month as ‘0’ in the
‘date’
format?

2008-00-00

I wouldn’t. Mysql doesn’t seem to care. PostgreSQL does.

philip=# create table foo (d date);
CREATE TABLE
philip=# insert into foo values (‘2008-00-00’);
ERROR: date/time field value out of range: “2008-00-00”
HINT: Perhaps you need a different “datestyle” setting.

7

Actually if you gonna use the date to make some things you can store
it as a simple integer, or as a date but not with 0 as day and month
but 1 as 0 is no month or day ^^

8

I don’t see a big advantage in storing it as a date, if you only want
to store the year why not store it as an integer? if then you need to
do some calculations you can always convert it to whatever you want.

On Feb 20, 11:56 pm, Scott H. [email protected]

9

Scott,

Why don’t you use the default created/updated_at fields and just
format the output to present only the year?

Cheers, Sazima

On Feb 20, 8:02 pm, Scott H. [email protected]

10

Store it as an integer, this will surely help you once you need to query
it.

Maurício Linhares
http://alinhavado.wordpress.com/ (pt-br) | http://blog.codevader.com/
(en)

On Fri, Feb 20, 2009 at 8:02 PM, Scott H.