Add timezone range to scope

Question

I have this scope that pulls orders for a rolling 14-day analytics graph. Problem is it pulls standard UTC, so sales show up as coming from tomorrow if a sale happens after 5pm PDT time (7 hour dif between UTC and PDT).

Current scope:

  scope :recent, complete.joins(:line_items)
    .group("DATE_TRUNC('day', processed_at)")
    .select("DATE_TRUNC('day', processed_at) as day, sum(line_items.discounted_price) as count")
    .order('day DESC').limit(14)

How can I make it so it only pulls orders 'processed_at' within the PDT time zone? Not sure how to do this syntactically, but basically I want to add 'in_time_zone' to the 'processed_at' timestamp.

When scope is called:

Order Load (2.7ms)  SELECT DATE_TRUNC('day', processed_at) as day, sum(line_items.discounted_price) as count FROM "orders" INNER JOIN "line_items" ON "line_items"."order_id" = "orders"."id" WHERE "orders"."status" = 'complete' GROUP BY DATE_TRUNC('day', processed_at) ORDER BY day DESC LIMIT 14

Answer 1

I run into similar situations all the time. The solution depends on which specific operation you're working with, but in all cases I prioritize writing fanatically thorough unit tests of the querying behavior (using Timecop or similar) to ensure that it's doing what I want it to be doing.

If you're doing this in a WHERE clause, it's easier because you can adjust the timestamp in Ruby. That might look something like this:

tz_adjusted_start_time = (Date.today - 14.days - 5.hours)
@result = Thingy.where("processed_at >= ?", tz_adjusted_start_time)

The above produces SQL that will look for records created after May 2nd 19:00:00 UTC, or whatever.

If you're trying to GROUP by date in a different time zone, then you need to do the adjustment in raw SQL and it can be a bit hairier to think your way through, but the same principle applies (and the same testing method works just as well). I've done this before and it was messy but I've run into no trouble with it so far. The SQL might look something like this:

...
GROUP BY (`processed_at` - INTERVAL 5 HOUR)
...

I seem to remember using this simple minus sign syntax, but a quick Google search tells me it's more common to use DATE_SUB (or find another way around the timezone issue altogether), so do your homework before considering this an end solution.

Good luck!