CODEWITHSUNDEEP
rubysqlitedatesequel
user2500392
user2500392

Reputation: 1

How to get the current month with Sequel

I would like recover a list of entries for the current month with Sequel.

I tried:

Entry.where(:date >= Date.month).sum(:duration)

or

Entry.where(:date.like('%/06/2013')).sum(:duration)

and other ways, but none of them seemed to work.

Upvotes: 0

Views: 570

Answers (2)

Jeremy Evans
Jeremy Evans

Reputation: 12139

If you want all entries the current month and the current year, it's probably easiest to use a range:

d = Date.today
Entry.where(:date=> Date.new(d.year, d.month)...(Date.new(d.year, d.month) >> 1)).sum(:duration)

If you want the current month in any year, Sequel has built in support for this:

Entry.where(Sequel.extract(:month, :date) => Date.today.month).sum(:duration)

Upvotes: 2

the Tin Man
the Tin Man

Reputation: 160551

You'll need to think in terms of how a database thinks, and how Sequel turns Ruby ranges into SQL:

require 'date'

today = Date.today                          # => #<Date: 2013-07-03 ((2456477j,0s,0n),+0s,2299161j)>
first_of_month = (today - today.day) + 1
first_of_month                              # => #<Date: 2013-07-01 ((2456475j,0s,0n),+0s,2299161j)>
next_month = today + 31                     # => #<Date: 2013-08-03 ((2456508j,0s,0n),+0s,2299161j)>
last_of_month = next_month - next_month.day # => #<Date: 2013-07-31 ((2456505j,0s,0n),+0s,2299161j)>
last_of_month                               # => #<Date: 2013-07-31 ((2456505j,0s,0n),+0s,2299161j)>

Entry.where(:date => [first_of_month .. last_of_month]).sum(:duration)

I'd show you the SQL output, but I don't know the database type you're using, and, well, I'm lazy.

Often, you can play tricks inside the DB by truncating "now" to remove the day, then finding all timestamps whose truncated date matches it. That's a lot more specific to the DBM than using Sequel, which already knows how to deal with ranges when converting them to a "between"-type statement.

Upvotes: 0

Related Questions