Querying active record objects from database based on created_at filters

I know that Ruby on rails stores all times in UTC (for created_at and updated_at fields) and when you fetch an active-record object from database, and ask RoR for it's date, it will convert it your configured (in environment.rb) Timezone and show you.

But my case is different. I am building a custom query. And I am adding a where clause manually to it. Where clause is such that: select * where created_at > [user entered date].

Now the problem that's arising is that the user entered date is in UTC - 7 and created_at is in UTC. So I can't really make it work. I could hardcode it like select * where created_at > [user-entered-date] 07:00:00 - but this created problem because of daylight savings, and also doesn't seem like a good solution.

This is not the only problem, the second problem is that when I print out the record.created_at, I am getting UTC date (perhaps because I build a custom query?), which also I don't want to manually (hardcode) convert to local time.

Here's my code for the query:

cond = EZ::Where::Condition.new

if !start_date.empty?
  start_date = params[:filter][:start_date].to_date.to_s(:db)
  cond.append "(registrations.created_at) >= '#{start_date} 07:00:00'" #Here!
end

if !end_date.empty?
  end_date = params[:filter][:end_date].to_date
  end_date = end_date + 1.day;
  end_date = end_date.to_s(:db)
  cond.append "(registrations.created_at) <= '#{end_date} 07:00:00'" #Here!
end

registrations = Registration.all(
:joins => [:event],
:select => 'registrations.id, registrations.first_name, registrations.last_name, registrations.company_name,
(registrations.created_at) AS reg_date, events.name AS evt_name, sum(fees) AS fees, code, events.id AS event_id',
:group => 'registrations.id',
:order => 'evt_name, events.id',
:conditions=> cond.to_sql
)
unless registrations.empty?

  registrations.each_with_index do |registration, i|
    sheet[ i, 3 ] = (DateTime.strptime(registration.reg_date, "%Y-%m-%d %H:%M:%S") - 7.hours).to_date #Here! 
  end
end

Answers


Try to use TimeWithZone and TimeZone

tz = ActiveSupport::TimeZone.new("Mountain Time (US & Canada)")
...
start_date = tz.local_to_utc(params[:filter][:start_date].to_time).to_s(:db)
...
sheet[ i, 3 ] = registration.reg_date.in_time_zone("Mountain Time (US & Canada)").to_date

Need Your Help

Where is Button.DialogResult in WPF?

wpf button dialogresult

In System.Windows.Forms.Button there is a property DialogResult, where is this property in the System.Windows.Controls.Button (WPF)?

How to find transformation matrix aligning two lines?

animation matrix xna transformation

Given two points in 3D space, A and B, I get a line segment LS. Given two new points A' and B' yielding the line segment LS', I need to find the transformation matrix that transforms LS into LS'. The