How to get Postgres records in higher date resolution (4 hours, 1 day, 1 week, 1 month - hourly, daily. monthly) from 1 hour timestamp table

Question

Given the following table, which is segmented into 1-hour intervals:

| id  | created_at                    | tt | vv  |
| --- | ----------------------------- | -- | --- |
| 1   | 2021-05-08 16:00:01.384687+00 | A  | 100 |
| 2   | 2021-05-08 16:00:03.435737+00 | B  | 50  |
| 3   | 2021-05-08 17:01:00.388957+00 | A  | 101 |
| 4   | 2021-05-08 17:02:00.43814+00  | B  | 49  |
| 5   | 2021-05-08 18:00:30.685336+00 | A  | 102 |
| 6   | 2021-05-08 18:00:40.731571+00 | B  | 48  |
| 7   | 2021-05-08 19:01:02.566949+00 | A  | 103 |
| 8   | 2021-05-08 19:02:00.576785+00 | B  | 47  |
| 9   | 2021-05-08 20:03:10.566949+00 | A  | 104 |
| 10  | 2021-05-08 20:05:00.576785+00 | B  | 46  |

where:

id is the primary key;
created_at is a timestamp with tz;
tt is a string / text; and
vv is a numeric

I am looking for the Postgres query string to obtain the data (created_at, tt, vv) segmented as 2-hour / 4-hour / 24-hour / 1-week / 1-month intervals. Example result table for 2-hour intervals would therefore be:

| id  | created_at                    | tt   | vv  |
| --- | ----------------------------- | ---- | --- |
| 1   | 2021-05-08 16:00:00.000000+00 | A    | 100 |
| 2   | 2021-05-08 16:00:00.000000+00 | B    | 50  |
| 5   | 2021-05-08 18:00:00.000000+00 | A    | 102 |
| 6   | 2021-05-08 18:00:00.000000+00 | B    | 48  |
| 9   | 2021-05-08 20:00:00.000000+00 | A    | 104 |
| 10  | 2021-05-08 20:00:00.000000+00 | B    | 46  |

I have tried utilizing a interval timeline as described here and combining it with a WITH statement unsuccessfully. NOTE: I am NOT looking for a sum column, but an aggregation as stated in the table above.

Output should therefore have the same structure as when using

SELECT date_trunc('second', created_at) as time, tt, vv FROM tbl GROUP BY created_at, tt, vv order by time desc

Bonus if interval can be flexibly specified in ms or s. Thank you!

nbk · Accepted Answer · 2021-05-12T18:13:20.583

You need to group by year,month day and hour, see belowexamples

CREATE TABLE table1 (
  "id" INTEGER,
  "created_at" timestamp,
  "tt" VARCHAR(1),
  "vv" INTEGER
);

INSERT INTO table1
  ("id", "created_at", "tt", "vv")
VALUES
  ('1', '2021-05-08 16:00:01.384687+00', 'A', '100'),
  ('2', '2021-05-08 16:00:03.435737+00', 'B', '50'),
  ('3', '2021-05-08 17:01:00.388957+00', 'A', '101'),
  ('4', '2021-05-08 17:02:00.43814+00', 'B', '49'),
  ('5', '2021-05-08 18:00:30.685336+00', 'A', '102'),
  ('6', '2021-05-08 18:00:40.731571+00', 'B', '48'),
  ('7', '2021-05-08 19:01:02.566949+00', 'A', '103'),
  ('8', '2021-05-08 19:02:00.576785+00', 'B', '47'),
  ('9', '2021-05-08 20:03:10.566949+00', 'A', '104'),
  ('10', '2021-05-08 20:05:00.576785+00', 'B', '46');

WITH CTE AS (
SELECT 
"id", "created_at", "tt", "vv",
  ROW_NUMBER () OVER (
      PARTITION BY "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 2)
      ORDER BY
          "created_at"
  ) as rown
  FROM table1)
SELECT "id", date_trunc('hour',"created_at"), "tt", "vv"
FROM CTE 
WHERE CTE.rown = 1
ORDER BY "id"

id | date_trunc          | tt |  vv
-: | :------------------ | :- | --:
 1 | 2021-05-08 16:00:00 | A  | 100
 2 | 2021-05-08 16:00:00 | B  |  50
 5 | 2021-05-08 18:00:00 | A  | 102
 6 | 2021-05-08 18:00:00 | B  |  48
 9 | 2021-05-08 20:00:00 | A  | 104
10 | 2021-05-08 20:00:00 | B  |  46

SELECT 
"id", "created_at", "tt", "vv",
  ROW_NUMBER () OVER (
      PARTITION BY "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 2)
      ORDER BY
          "created_at"
  ) as rown
  FROM table1

id | created_at                 | tt |  vv | rown
-: | :------------------------- | :- | --: | ---:
 1 | 2021-05-08 16:00:01.384687 | A  | 100 |    1
 3 | 2021-05-08 17:01:00.388957 | A  | 101 |    2
 5 | 2021-05-08 18:00:30.685336 | A  | 102 |    1
 7 | 2021-05-08 19:01:02.566949 | A  | 103 |    2
 9 | 2021-05-08 20:03:10.566949 | A  | 104 |    1
 2 | 2021-05-08 16:00:03.435737 | B  |  50 |    1
 4 | 2021-05-08 17:02:00.43814  | B  |  49 |    2
 6 | 2021-05-08 18:00:40.731571 | B  |  48 |    1
 8 | 2021-05-08 19:02:00.576785 | B  |  47 |    2
10 | 2021-05-08 20:05:00.576785 | B  |  46 |    1

SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 2)

min | min                        | tt | sum
--: | :------------------------- | :- | --:
  5 | 2021-05-08 18:00:30.685336 | A  | 205
  1 | 2021-05-08 16:00:01.384687 | A  | 201
 10 | 2021-05-08 20:05:00.576785 | B  |  46
  6 | 2021-05-08 18:00:40.731571 | B  |  95
  2 | 2021-05-08 16:00:03.435737 | B  |  99
  9 | 2021-05-08 20:03:10.566949 | A  | 104

SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")
,trunc(EXTRACT(hour from  "created_at") / 4)

min | min                        | tt | sum
--: | :------------------------- | :- | --:
  1 | 2021-05-08 16:00:01.384687 | A  | 406
  9 | 2021-05-08 20:03:10.566949 | A  | 104
 10 | 2021-05-08 20:05:00.576785 | B  |  46
  2 | 2021-05-08 16:00:03.435737 | B  | 194

;SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(day from  "created_at")

min | min                        | tt | sum
--: | :------------------------- | :- | --:
  1 | 2021-05-08 16:00:01.384687 | A  | 510
  2 | 2021-05-08 16:00:03.435737 | B  | 240

;SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")
,EXTRACT(week from "created_at")

min | min                        | tt | sum
--: | :------------------------- | :- | --:
  1 | 2021-05-08 16:00:01.384687 | A  | 510
  2 | 2021-05-08 16:00:03.435737 | B  | 240

;SELECT 
MIN("id"), MIN("created_at"), "tt", SUM("vv")

FROM table1 
group by "tt"
,EXTRACT(year from  "created_at")
,EXTRACT(Month from  "created_at")

min | min                        | tt | sum
--: | :------------------------- | :- | --:
  2 | 2021-05-08 16:00:03.435737 | B  | 240
  1 | 2021-05-08 16:00:01.384687 | A  | 510

db<>fiddle here

All columns in the Select must be in the GROUP By or have a aggregation function, the sum and MIN are only for demonstration purposes else it would give an error. ,but that wasn't your question, if you a´want to know something else then dates ask an new one and accept the answer — nbk, May 08 '21 at 22:50
I gave an example query for the result (which includes vv, not sum) and an example result table (which includes vv, not sum). — pspray, May 08 '21 at 22:53
I'd like to accept the answer and appreciate the effort but it's just not useful without the vv column. — pspray, May 08 '21 at 23:16
I have accepted the answer because it provides the segmentation in combination with a "SELECT * from x" query. I would still be looking for a solution that outputs the result required above in ONE query. Thank you for the effort! — pspray, May 12 '21 at 08:52
add a row number to the result and then select the first i think, after work i make a sample if you have a problem with it — nbk, May 12 '21 at 10:13
@pspray see the first query it shows you how to wsolve your wanted result, the rest espeial the part where you partition, you find in the select privious posted — nbk, May 12 '21 at 18:14

How to get Postgres records in higher date resolution (4 hours, 1 day, 1 week, 1 month - hourly, daily. monthly) from 1 hour timestamp table

1 Answers1