in rails i need to get all records based on a date range, ignoring the year

user_12968081 0
Viewed 220

i am trying to get all records within a date range in rails that fall after a given start date and before a given end date, ignoring the year. the start date will just be a month. the end of the date range is a month and date. the following example is to get users with a hire date month equal to january or greater, but less than 2(february) / 28(day). i was trying this but it doesnt work:

users.where('extract(month from hire_date) >= ?', 1).where('extract(month from hire_date) <= ? AND extract(day from hire_date) <= ?', 2, 28)

the reason this doesnt work is it will exclude users, for example who fall on 1/29, because their day in january is not less than 28.

is there a good way to make this work, ignoring the year on the date field?

activerecord ruby-on-rails ruby sql
3 Answers

Let me start by saying that I'm not familiar with ruby-on-rails, sql, or activerecord but this question really piqued my interest because it contains a number of complexities that I've never had to deal with. Most notably:

  • How to accommodate leap years when the test range and target date may have differing leap year statuses.
  • How to deal with a hire date of February 29th. This may seem to be the same as the first item on my list, but it has its own unique nuances.
  • How to deal with ranges that started in one year and then overlap into the next.

I did some searching myself and there seems to be very little information out there on the subject, particularly any that seems to properly deal with the various complexities listed above, so I decided to see if I could come up with a logical approach of my own. It seems like it may be a novel approach, but ultimately I decided that converting month and day to float values would allow an easy way to address the leap year issue as well as the issue with February 29th, and that testing the date ranges using an if/else statement along with opposing boolean operations would solve the ranges that overlap years.

You may have to extrapolate and reconfigure for your specific needs, but here's the general idea written out in pure ruby. It's rather verbose, but I did so just to try and make it more clear what I'm doing. I could be made much more compact:

hire_date = Time.new(2004, 1, 22)

provided_start_date = Time.new(2008, 12, 22)

day_value = (60*60*24)  #60 seconds * 60 minutes * 24 hours = 1 day
num_days = 30

calculated_end_date =  provided_start_date + num_days*day_value

start_range = provided_start_date.month + provided_start_date.day/100.0
end_range = calculated_end_date.month + calculated_end_date.day/100.0
day_float = hire_date.month + hire_date.day/100.0

if start_range < end_range
  result = day_float >= start_range and day_float <= end_range
else
  result = day_float >= start_range or day_float <= end_range
end

result
user_164522280

I would use to_char to transform the dates into strings including just the month and the day. For example 2021-12-31 could be translated into "12-31". That string can then be compared to the range of date strings you are interested in.

users.where("to_char(hire_date, 'MM-DD') BETWEEN '01-01' AND '02-29'")

Note that this, in theory, this would also match invalid date strings like '01-40'. But I guess it is safe to assume that to_char with a valid date will never return such invalid date strings.

user_24833130

So i figured out something that worked for this. given the following:

start_date = 01 Jan 2021
end_date = 02 Feb 2021

Users.where("(extract(month from hire_date) * 100 + extract(day from hire_date)) BETWEEN ? AND ?", ((start_date.month * 100) + start_date.day), ((end_date.month * 100) + end_date.day))

start date and end date can be any dates and this should work to get all users between, since SQL between is inclusive, it will also get users whos end and start dates fall on the start / end date, ignoring year.

user_129680810
Related