Forum: Ruby on Rails Storing just the year in the DB

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Scott H. (Guest)
on 2009-02-21 01:02
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
CiriusMex (Guest)
on 2009-02-21 01:26
(Received via mailing list)
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. <removed_email_address@domain.invalid>
Scott H. (Guest)
on 2009-02-21 01:32
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. <removed_email_address@domain.invalid>



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
-------------
CiriusMex (Guest)
on 2009-02-21 01:50
(Received via mailing list)
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. <removed_email_address@domain.invalid>
Scott H. (Guest)
on 2009-02-21 01:56
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. <removed_email_address@domain.invalid>

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
Philip H. (Guest)
on 2009-02-21 02:13
(Received via mailing list)
>> 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.
CiriusMex (Guest)
on 2009-02-21 02:40
(Received via mailing list)
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 ^^
Miguel R. (Guest)
on 2009-02-21 16:22
(Received via mailing list)
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. <removed_email_address@domain.invalid>
Sazima (Guest)
on 2009-02-22 22:43
(Received via mailing list)
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. <removed_email_address@domain.invalid>
Maurício L. (Guest)
on 2009-02-23 04:28
(Received via mailing list)
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.
This topic is locked and can not be replied to.