1

Is there a way to store days, weeks, or months in the database. I'm not talking about dates (like December 13, 2012), but about amounts of time, like 2 weeks, 5 days, or 6 months.

One of the suggestions I found was this: Best way to store time (hh:mm) in a database, in which you store minutes in the database as integers and manipulate them as you need.

Another one, Best way to store span on time in a MySQL database?, talked about simply using a time datatype, but this has a very low range limit.

Is there a more clean way to accomplish this? Thanks

Community
  • 1
  • 1
sauronnikko
  • 4,665
  • 5
  • 31
  • 47

2 Answers2

2

yes, there is a cleaner way to do it. just use postgres and the built in datatype INTERVAL: http://www.postgresql.org/docs/6.3/static/c0804.htm

phoet
  • 18,688
  • 4
  • 46
  • 74
1

For rails I've had the most success simply storing an integer field in my models to represent a accumulated time in seconds. Depending on your use case you might have more success with storing integer minutes instead.

To demonstrate with seconds you can then do something like:

rails g migration add_seconds_elapsed_to_foo seconds_elapsed:integer

Somewhere in your application:

@foo.seconds_elapsed
 => 2343 
distance_of_time_in_words(Time.now, Time.now + @foo.seconds_elapsed.seconds, true)
 => "39 minutes"

In conjunction with the time object there's a number of options you can take for a variety of precisions and formats.

Noz
  • 6,216
  • 3
  • 47
  • 82