test.rb

ActiveRecord::Base.logger = Logger.new('/dev/null')

# function APP_TIME()
# The current time matching the timing of events in the database (UTC)
sql = <<-SQL
  CREATE OR REPLACE FUNCTION app_time()
  RETURNS timestamp as $$
    SELECT (localtimestamp AT TIME ZONE current_setting('TimeZone') AT TIME ZONE 'UTC')::timestamp
  $$ LANGUAGE SQL IMMUTABLE STRICT;
SQL
ActiveRecord::Base.connection.execute(sql)

# function COMPANY_TIME()
# The current company time (CST)
sql = <<-SQL
  CREATE OR REPLACE FUNCTION company_time()
  RETURNS timestamp as $$
    SELECT (localtimestamp AT TIME ZONE current_setting('TimeZone') AT TIME ZONE 'America/Chicago')::timestamp
  $$ LANGUAGE SQL IMMUTABLE STRICT;
SQL
ActiveRecord::Base.connection.execute(sql)

# function BEGINNING_OF_DAY
# Timestamp at beginning of the day of a given timestamp.
# Analogous to Time#beginning_of_day in ruby.
sql = <<-SQL
  CREATE OR REPLACE FUNCTION beginning_of_day(timestamp)
  RETURNS timestamp as $$
    select date_trunc('day', $1)
  $$ LANGUAGE SQL IMMUTABLE STRICT;
SQL
ActiveRecord::Base.connection.execute(sql)

# function BEGINNING_OF_HOUR
# Timestamp at beginning of the hour of a given timestamp.
# Analogous to Time#beginning_of_hour in ruby.
sql = <<-SQL
  CREATE OR REPLACE FUNCTION beginning_of_hour(timestamp)
  RETURNS timestamp as $$
    select date_trunc('hour', $1)
  $$ LANGUAGE SQL IMMUTABLE STRICT;
SQL
ActiveRecord::Base.connection.execute(sql)

# function END_OF_HOUR
# Timestamp at end of the hour of a given timestamp.
# Analogous to Time#end_of_hour in ruby.
sql = <<-SQL
  CREATE OR REPLACE FUNCTION end_of_hour(timestamp)
  RETURNS timestamp as $$
    select beginning_of_hour($1) + interval '1 hour' - interval '0.000001 seconds'
  $$ LANGUAGE SQL IMMUTABLE STRICT;
SQL
ActiveRecord::Base.connection.execute(sql)

# Function OFFSET_BY_COMPANY_TIME
# Shifts the timestamp so that it can constrain results as to
# the company time.
sql = <<-SQL
  CREATE OR REPLACE FUNCTION offset_by_company_time(timestamp)
  RETURNS timestamp as $$
    select $1 - (select utc_offset from pg_timezone_names where name='America/Chicago')::interval
  $$ LANGUAGE SQL IMMUTABLE STRICT;
SQL
ActiveRecord::Base.connection.execute(sql)

# Dates for our tests
start_date = Time.now.in_time_zone('America/Chicago').beginning_of_day
end_date = start_date.end_of_hour

# Get dates for our tests from the database
sql = <<-SQL
select  offset_by_company_time(beginning_of_day(company_time())) as start_time,
        end_of_hour(offset_by_company_time(beginning_of_day(company_time()))) as end_time;
SQL
results = ActiveRecord::Base.connection.select_all(sql).first

# Display the dates
puts "AR date range: #{ActiveRecord::Base.sanitize(start_date)}..#{ActiveRecord::Base.sanitize(end_date)}"
puts "SQL date range #{results['start_time']}..#{results['end_time']}"

# Fetch a count within range via AR
ar_count = Shipment.where('label_purchased_at between ? and ?', start_date, end_date).count

# Fetch a count within range via raw sql
sql = <<-SQL
select
  count(*) as count
from
  shipments
where
  type in ('Shipment')
and
  label_purchased_at
between
  offset_by_company_time(beginning_of_day(company_time()))
and
  end_of_hour(offset_by_company_time(beginning_of_day(company_time())));
SQL

sql_count = ActiveRecord::Base.connection.select_all(sql).first['count'].to_i


# output our counts
puts "AR count: #{ar_count}"
puts "SQL count: #{sql_count}"

Output on app04

2.1.2 :001 > load File.expand_path("~/test.rb")
Queries being logged to console_20150624-041302GMT.log
AR date range: '2015-06-23 05:00:00.000000'..'2015-06-23 05:59:59.999999'
SQL date range 2015-06-23 05:00:00..2015-06-23 05:59:59.999999
AR count: 579
SQL count: 579