1

I am finding hard time figuring out a issue while trying to find records created this month.

my query in controller looks like this

@sh = SupportHistory.where('created_at BETWEEN ? AND ?', Time.now.beginning_of_month, DateTime.now.end_of_month)

my sql looks like this

SELECT `support_histories`.* FROM `support_histories`  WHERE (created_at BETWEEN '2012-10-31 18:30:00' AND '2012-11-30 18:29:59')

The problem is its finding a record from previous month, how to fix it ?

Update

I was able to figure-out the problem through this link: Timezone with rails 3

Now my query in controller looks like this

@sh = SupportHistory.where('created_at BETWEEN ? AND ?', DateTime.now.in_time_zone.beginning_of_month, DateTime.now.in_time_zone.end_of_month)

Now my sql looks like this

SELECT `support_histories`.* FROM `support_histories`  WHERE (created_at BETWEEN '2012-11-01 05:
00:00' AND '2012-12-01 05:59:59')

Now i have another Question, is there a way to avoid 'in_time_zone' ? or to make it happen automatically ? :)

Community
  • 1
  • 1
Syed
  • 15,657
  • 13
  • 120
  • 154
  • 1
    `created_at` is in UTC but your timezone is 5.5 hours away from UTC so what's the problem (other then the unintended gap on the upper bound)? – mu is too short Nov 04 '12 at 06:32
  • Your comment gave me the hint that there was problem in time zone. Thanks. – Syed Nov 04 '12 at 09:11
  • 1
    Not sure if this works, but have you tried setting the default timezone? See this thread: http://stackoverflow.com/questions/6118779/how-to-change-default-timezone-for-activerecord-in-rails3 – aguynamedloren Nov 04 '12 at 09:44
  • 1
    You should use `created_at >= ? and created_at < ?` with the beginning of this month and the beginning of next month as values. SQL's BETWEEN works with closed intervals (`[a,b]`) but you want a half-open (`[a,b)`) interval to avoid a problem with fractional seconds in the database (2012-12-01T05:59:59.42 for example). – mu is too short Nov 04 '12 at 17:51

0 Answers0