Set new year for date column in rails query

user_4833129 0
Viewed 94

I have the contract start of a number of companies, and I want to report on each contract year by creating a column with the contract start updated to a select year. There are a number of solutions in SQL involving functions like DATE_ADD or DATEFROMPARTS, but I'm having trouble adapting it to rails (if those functions are available at all).

The closest I've gotten is: Company.select("contract_start + '1 YEAR'::INTERVAL as new_contract_start"). This adds 1 year to each contract start but doesn't take into account contracts older than a year (or started the same year). I've also tried the following but again run into syntax errors:

new_year = 2020
Company.select("contract_start + '#{new_year} - EXTRACT (YEAR from contract_start) YEAR'::INTERVAL")

I'm looking for a solution that can either:

  1. Directly set the year to what I want
  2. Add a variable amount of years based on its distance from the desired year

I'm on Ruby 2.3.3

datetime activerecord ruby-on-rails sqlite ruby
1 Answers

I think the key here was finding functions compatible with the PostgreSQL that my database was built on. Once I started searching for the functions I thought would help and their PostgreSQL equivalents, I found more compatible solutions, such as: NUMTODSINTERVAL in PostgreSQL

I ended up with:

contract_start_year = 2020
Company.select("contract_start + make_interval(years => CAST (#{contract_start_year} - EXTRACT (YEAR from contract_start) as INT))

I've also made it a bit smarter by adding the number of years required to get the latest contract date without going over the report date. This would be problematic if the report start date was "2020-01-01" but the contract start was "2017-06-01". Setting the contract date to "2020-06-01" would overshoot the intentions of the report.

report_start = "`2020-07-01`"
Company.select("contract_start + make_interval(years => CAST (EXTRACT (YEAR FROM AGE(CAST (#{start_quotations} AS DATE), contract_start)) AS INT)) as new_contract_year")

Note the additional single quotes in report_start since the SQL code need to read a string to convert it to a date

There might be other methods that can "build" the date directly, but this methods works well enough for now.

user_48331290
Related