1

I have a time field in table "timestamp without time zone". When record is saved to database, the utc time might be a different day compared to the local time. However, I need to group the records by date. Hence, I am doing something like this:

result = transmissions.joins(:report).where('reports.time::timestamp::date = ?', record.time.to_date)

The problem is if the utc date is on a different date than local time, then that record is not included in result. Any ideas how to get the right result?

And apparently I cannot change the "without time zone" either:

Rails database-specific data type

It says: "concluded that the default ActiveRecord datetime and timestamp column types in schema migrations cannot be modified to force PostgreSQL to use timestamp with time zone."

So I have no idea how to group by date, as obviously something like this is wrong:

Unit.where(id: 1100).first.reports.order("DATE(time)").group("DATE(time)").count
 => {"2013-12-14"=>19, "2013-12-15"=>5}

That return value is completely wrong. All 25 records should be on 2013-12-14 and 0 records on 2013-12-15.

Community
  • 1
  • 1
JohnMerlino
  • 3,900
  • 4
  • 57
  • 89
  • Here is a blog post i wrote on rails and timezones http://jessehouse.com/blog/2013/11/15/working-with-timezones-and-ruby-on-rails/ - it does not cover your situation, but i am thinking you might need to use postgres 'at time zone' or do the grouping in ruby – house9 Dec 15 '13 at 23:08

2 Answers2

1

Assuming your records are timestamped with a particular UTC offset, you can try passing in the start and end times of the date in question in UTC format to your query:

result = transmissions.joins(:report).where('reports.time >= ? AND reports.time < ?', record.time.midnight.utc, (record.time.midnight + 1.day).utc)

Explanation:

midnight is a Rails method on an instance of Time that returns the Time object that represents midnight on the date of the original Time object. Similarly, record.time.midnight + 1.day returns the Time object representing midnight of the following day. Then, converting both Time objects – which are presumably timestamped in a standard UTC offset – to UTC creates a time period representing midnight-to-midnight for the system timezone in UTC format (not midnight in UTC time), which is precisely what you're seeking to query.

zeantsoi
  • 25,857
  • 7
  • 69
  • 61
  • This seemed to have worked. Can you explain a little more what's going on with "midnight.utc"? – JohnMerlino Dec 15 '13 at 23:23
  • Sure – explanation attached. Since this worked, would you kindly consider upvoting/accepting this question as correct? – zeantsoi Dec 15 '13 at 23:33
0

How about something like result = transmissions.joins(:report).where('reports.time >= ? AND reports.time <= ?', record.time.beginning_of_day.utc, record.time.end_of_day.utc)

The .utc part may not be necessary.

JosephL
  • 5,952
  • 1
  • 28
  • 25