Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get current month/year and next month/year?

I'm trying to pull all billing info that has fields matching the current month/year or the next month year.

For instance, say the current month was December 2014, I'd want something like this:

Billing.where(exp_month: 12, exp_year: 2014 OR exp_month: 1, exp_year: 2015)

That syntax is obviously incorrect, but it gives you an idea of what I'm after.

So, the questions here are...

  1. How do I format that query correctly?
  2. How do I get the current/next month/year for that query format?

I'm running Ruby 2.1.2.

like image 567
Shpigford Avatar asked Jul 21 '14 13:07

Shpigford


2 Answers

The Date class of Ruby offers a lot of methods:

first_of_month = Date.current.beginning_of_month
last_of_next_month = (Date.current + 1.months).end_of_month
Billing.where('your_date_field BETWEEN ? AND ?', first_of_month, last_of_next_month)

You want it to work with DateTime ?

first_of_month = Date.current.beginning_of_month.beginning_of_day
last_of_next_month = (Date.current + 1.months).end_of_month.end_of_day
Billing.where('your_date_field BETWEEN ? AND ?', first_of_month, last_of_next_month)

If you want something more complex, I suggest you to use PostgreSQL's date/time functions: http://www.postgresql.org/docs/9.1/static/functions-datetime.html

conditions = []
conditions << ["date_part('year', your_date_field) = '2014'", "date_part('month', your_date_field) = '12')"]
conditions << ["date_part('year', your_date_field) = '2015'", "date_part('month', your_date_field) = '01')"]
conditions = conditions.map do |conds|
  " ( #{conds.join(' AND ')} ) "
end.join(' OR ')
# => " ( date_part('year', your_date_field) = '2014' AND date_part('month', your_date_field) = '12') )  OR  ( date_part('year', your_date_field) = '2015' AND date_part('month', your_date_field) = '01') ) "
Billing.where(conditions)
like image 138
MrYoshiji Avatar answered Oct 16 '22 05:10

MrYoshiji


you could try this

 Date.today.strftime("%m") # for month
 Date.today.strftime("%Y") # for year
like image 27
Jeet Avatar answered Oct 16 '22 06:10

Jeet