Rails query data by hour

Question

I'm having difficulty querying data by an hour for the previous day. Using Rails 4 on Postgres.

eg,

Table X:

created_at, value

timestamp1    3
timestamp2    5

I want to get:

 time, value
YYMMDD 00:00   15
YYMMDD 01:00   20

basically the sum per hour. I've tried

but I still cant figure out how to get it to work properly, i get the wrong hour for replies. How would i set my timezone along with this query?

TableX.group("DATE_trunc('hour',created_at)").count

Thanks!

The link you post above is useful and able to solve your problem. It would be better if you can provide more info about obstacle you are encountering, like codes, error logs, etc. — Eason Caizhen Liu, Aug 19 '16 at 07:51
Possible duplicate of [Rails & Postgresql: how to group queries by hour?](http://stackoverflow.com/questions/15182051/rails-postgresql-how-to-group-queries-by-hour) — Roman Kiselenko, Aug 19 '16 at 07:55
Hey thanks for your reply! I get {16.0=>11} when I query for test data, I dont think this is the right timezone. How would I set the timezone? — Wboy, Aug 19 '16 at 07:55
as @EasonCaizhenLiu point. The link you post above is useful and able to solve your problem. — Roman Kiselenko, Aug 19 '16 at 07:58
@Зелёный Respectfully, it does not. I would hesitate to post a new question if it did. The answer posted below solves it, but the timezone for the string returns wrongly — Wboy, Aug 19 '16 at 08:10
This can be tried out -> [groupdate gem](https://github.com/ankane/groupdate) — Ashik Salman, Feb 28 '18 at 10:31

score 1 · Accepted Answer · answered Aug 19 '16 at 08:01

1

All you need is create a query like this one:

SELECT date_trunc('hours', created_at), sum(value)
FROM TableX
GROUP BY date_trunc('hours', created_at)

This one uses date_trunc instead of suggested date_part, which fits your question better.

answered Aug 19 '16 at 08:01

Boris Schegolev

THANK YOU! how would I be able to set the timezone in this query? – Wboy Aug 19 '16 at 08:03
Timezone is a connection-wide session property. Just run SET TIME ZONE 'Europe/Rome'; after connecting. – Boris Schegolev Aug 19 '16 at 08:06
Thats the SQL side, yes? Im using the Rails ORM though, and setting timezone here might affect the rest of the app. is there no way to query just with the zone alone? this is what i have so far: TableX.group("DATE_trunc('hour',created_at)").count – Wboy Aug 19 '16 at 08:09
I am not sure it is a good idea to define timezones for individual queries. You should consider setting a global timezone - check on this with other developers. – Boris Schegolev Aug 19 '16 at 08:26

1 Answers1