1

I have an issue that I want to count total data with 1 hour interval. However start time is 7:30

Time Status 2022-03-24 07:36:00, Fail 2022-03-24 07:59:00, Pass 2022-03-24 09:32:00, Pass 2022-03-24 09:41:00, Pass 2022-03-24 10:02:00, Fail 2022-03-24 11:02:00, Pass 2022-03-24 11:22:00, Fail

You can see that before 8:30 I have 2 data that 1 pass(07:59) and 1 fail(07:36) so I can count it and create data below

Time Status Total 2022-03-24 08:30 Pass 1 2022-03-24 08:30 Fail 1 2022-03-24 09:30 Pass 0 2022-03-24 09:30 Fail 0 2022-03-24 10:30 Pass 2 2022-03-24 10:30 Fail 1 2022-03-24 11:30 Pass 1 2022-03-24 11:30 Fail 1
Improve this question

1 Answer 1

Reset to default
1

It sounds like you want group your datapoints with date_bin, which allows you to specify an origin time and a stride interval to use as an offset.

SELECT date_bin('1 hour', Time, TIMESTAMP '2022-03-24 07:30:00') as Time, Status, count(*) as Total FROM datapoints GROUP BY 1, 2 ORDER BY 1, 2 DESC
Improve this answer
4
  • Thanks for your answer, however when I change your timestamp to '2022-03-24 10:30:00' sql query has the same data with '2022-03-24 07:30:00' Commented Mar 24, 2022 at 3:44
  • Do you only want datapoints after that timestamp? Commented Mar 24, 2022 at 3:58
  • Yes, even I can do it with Where clause, but it will be better if change timestamp in date_bin make query clear Commented Mar 24, 2022 at 4:03
  • After doing some example with date_bin, maybe I understand the purpose of timestamp xD thank you so much for helping me xD Commented Mar 24, 2022 at 4:19

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.