CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
gonzag88
gonzag88

Reputation: 101

How to store time duration with PostgreSQL and Ruby on Rails?

We want users to register the amount of time they work on something. For example, let's say I've worked two hours and a half for a project, then I want to fill a form input with 2:30 --two hours and thirty minutes-- and store it in the database. The same for 1:45 --one hour and forty five minutes-- to bring another case. Then we will use that information to calculate the amount of money that should be assigned to that activity.

For example, if one hour is 10 USD, then:

Should we convert 2:30 to seconds to store it in the database? Or is there a better solution?

Thanks!

Upvotes: 1

Views: 6027

Answers (3)

Vao Tsun
Vao Tsun

Reputation: 51426

with Postgres I would consider ideao of using interval data type, eg:

t=# with t(v) as (values('2:30'::text),('2 12:31:00'::text),('38 hours 21 minutes 4 seconds'))
select v, v::interval,justify_interval(v::interval),extract(epoch from v::interval) from t;
               v               |        v        | justify_interval | date_part
-------------------------------+-----------------+------------------+-----------
 2:30                          | 02:30:00        | 02:30:00         |      9000
 2 12:31:00                    | 2 days 12:31:00 | 2 days 12:31:00  |    217860
 38 hours 21 minutes 4 seconds | 38:21:04        | 1 day 14:21:04   |    138064
(3 rows)

Upvotes: 0

coreyward
coreyward

Reputation: 80041

It's better to avoid floating point for discrete values. Since the smallest meaningful value to you is minutes, that's what I would recommend storing the time in. Storing seconds provides unnecessary resolution (more data), and storing hours either gets you into storing decimals or means you don't have enough precision.

How you store the value depends on which version of Rails you're on. If 5.1, there is native support for the interval column types in Postgres. I would use it if you have it, but if you don't it's not a dealbreaker, just use an integer column.

Upvotes: 5

mike927
mike927

Reputation: 774

What about?

Time.parse("0:15").seconds_since_midnight / 3600 * 10 => 2.5

Personally I would store number of seconds instead of time type.

Upvotes: 2

Related Questions