test=# create table dash as select s, '2017-07-10'::date + s * '1hour'::interval as zeit from generate_series(0,47) s;
SELECT 48
test=*# select zeit::date, array_agg(s) from dash group by 1;
zeit | array_agg
------------+---------------------------------------------------------------------------
2017-07-11 | {24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47}
2017-07-10 | {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23}
(2 Zeilen)
test=*# select (zeit + '5hour'::interval)::date, array_agg(s) from dash group by 1;
date | array_agg
------------+---------------------------------------------------------------------------
2017-07-11 | {19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42}
2017-07-10 | {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}
2017-07-12 | {43,44,45,46,47}
(3 Zeilen)
test=*# select * from dash ;
s | zeit
----+---------------------
0 | 2017-07-10 00:00:00
1 | 2017-07-10 01:00:00
2 | 2017-07-10 02:00:00
3 | 2017-07-10 03:00:00
4 | 2017-07-10 04:00:00
5 | 2017-07-10 05:00:00
6 | 2017-07-10 06:00:00
7 | 2017-07-10 07:00:00
8 | 2017-07-10 08:00:00
9 | 2017-07-10 09:00:00
10 | 2017-07-10 10:00:00
11 | 2017-07-10 11:00:00
12 | 2017-07-10 12:00:00
13 | 2017-07-10 13:00:00
14 | 2017-07-10 14:00:00
15 | 2017-07-10 15:00:00
16 | 2017-07-10 16:00:00
17 | 2017-07-10 17:00:00
18 | 2017-07-10 18:00:00
19 | 2017-07-10 19:00:00
20 | 2017-07-10 20:00:00
21 | 2017-07-10 21:00:00
22 | 2017-07-10 22:00:00
23 | 2017-07-10 23:00:00
24 | 2017-07-11 00:00:00
25 | 2017-07-11 01:00:00
26 | 2017-07-11 02:00:00
27 | 2017-07-11 03:00:00
28 | 2017-07-11 04:00:00
29 | 2017-07-11 05:00:00
30 | 2017-07-11 06:00:00
31 | 2017-07-11 07:00:00
32 | 2017-07-11 08:00:00
33 | 2017-07-11 09:00:00
34 | 2017-07-11 10:00:00
35 | 2017-07-11 11:00:00
36 | 2017-07-11 12:00:00
37 | 2017-07-11 13:00:00
38 | 2017-07-11 14:00:00
39 | 2017-07-11 15:00:00
40 | 2017-07-11 16:00:00
41 | 2017-07-11 17:00:00
42 | 2017-07-11 18:00:00
43 | 2017-07-11 19:00:00
44 | 2017-07-11 20:00:00
45 | 2017-07-11 21:00:00
46 | 2017-07-11 22:00:00
47 | 2017-07-11 23:00:00
(48 Zeilen)
test=*#