0

I have to make a query to fetch and then count all records with created_at, by Date in the time zone:

def total_by_date(my_date)    
  where{created_at <= my_date}.count
end

created_at is stored as timestamp in UTC in PostgreSQL, but my_date is a Date in Rails's application time zone.

How it could be done right way, without losses because of time zone conversion, and should created_at be converted to Date first?

Leo Burt
  • 109
  • 1
  • 6
  • I'm a little confused about the `where` clause. Is that `total_by_date` being invoked as a scope? Are you using any kind of gems for queries? – Paul Richter Sep 27 '13 at 14:44

1 Answers1

1

The big problem with a plain 'ol Date object is that it contains no time information. So in your query as you presently have it, you would end up getting everything BEFORE the specified date, since the query would resolving to something like WHERE created_at <= '2013-09-27'.... One way you can get around this problem without losing records is to simply add 1 day to your date object, like so:

def total_by_date(my_date)    
    where{created_at <= (my_date + 1.day)}.count
end

In this way too you don't need to worry about timezones at all; you're just looking at the date portion.

**Edit**

In response to your question in the comment:

Regarding why the + 1.day works, think of the date like this: my_date comes out looking like 2013-09-24 in the query, but it is basically the same as 2013-09-24 00:00:00. That would be the same as a datetime/timestamp set to midnight on September 24. So, in plain English, if your comparison is "give me everything on or before midnight September 24", you'll get every result that lands on, and exists earlier than that specific time and date, but anything that happened later in the day on Sept 24 won't show up.

Hence the + 1.day - the same "problem" still exists, except it works in your favour now because our query is now "anything on or before midnight September 25".

Note: Technically, you might get undesired matches for anything that happened to be created on exactly midnight September 25, but you can easily change your comparison to simply < instead of <= to fix that problem.

Paul Richter
  • 10,908
  • 10
  • 52
  • 85
  • Hey Teeg, thanks for your answer. Yea, I forgot to mentioned Squeel here, so generally, everything is fine with the query by itself. Now I'm trying to wrap my head around `+ 1.day` stuff, and figure out why it works (it is! well.. looks like...). Cannot get, why if i for example have `2013-09-24` as `my_date` and `2013-09-24 13:06:22` as `created_at`, `my_date` will be before the specified date. Weird. – Leo Burt Sep 27 '13 at 15:40
  • 1
    Think of the date like this: `my_date` comes out looking like `2013-09-24`, but it is basically the same as `2013-09-24 00:00:00`. That would be the same as a datetime/timestamp set to midnight on September 24. So, if your comparison is "anything on or before midnight September 24", you'll get every result that lands on, and exists earlier than that time and date, but anything that happened later in the day on Sept 24 won't show up. Hence the ` + 1.day` - the same "problem" still exists, except it works in your favour now because our query is now "anything on or before midnight September 25". – Paul Richter Sep 27 '13 at 15:48
  • @LeoBurt Added more clarification to the answer. Hope that helps. – Paul Richter Sep 27 '13 at 15:51
  • Oh, i see. I really appreciate your support. Kudos. – Leo Burt Sep 27 '13 at 15:55
  • @LeoBurt No prob. Good luck. – Paul Richter Sep 27 '13 at 15:56