CODEWITHSUNDEEP
ruby-on-railspostgresqlutc
JohnMerlino
JohnMerlino

Reputation: 3928

rails group by utc date

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.

Upvotes: 1

Views: 475

Answers (2)

zeantsoi
zeantsoi

Reputation: 26193

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.

Upvotes: 1

JosephL
JosephL

Reputation: 5973

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.

Upvotes: 0

Related Questions