Select mit group by auf zeitbereiche (z.b. stundenweise, 15 minuten,...)

Hallo!

Durch kurzfristige Umstellung von mysql auf PostgreSQL benötige ich eine adäquate Funktionalität zu meinem mysql-Statement. In diesem Statement hole ich mir die Daten nach gewissen zeitbereichen (z.B. gesammelte Daten von 15 Minuten). Das mysql-Statement sieht dabei folgendermaßen aus:

Code:
SELECT FROM_UNIXTIME(
CEILING(UNIX_TIMESTAMP(St_TimeStamp)/900)900) AS timeslice, COUNT() AS mycount
FROM statistic
WHERE St_TimeStamp >= ‘2010-12-16 09:00:00’
AND St_TimeStamp < ‘2010-12-18 16:30:00’
GROUP
BY timeslice
In Postgree gibt es diese UNIX_TIMESTAMPS nicht, ich habe auch schon mit diversen TIMESTAMPS probiert, z.B.

Code:
TIMESTAMP ‘epoch’ + St_TimeStamp * INTERVAL ‘1 second’
komme aber auf kein Ergebnis. Die Abfrage soll mir an erster Stelle den Zeitstempel in einem korrekten Datumsformat liefern. Kann mir hierbei jemand helfen, wie ich mein Statement umbauen muss?

Posting auch hier ersichtlich:
Zeitbereich in PostgreSQL ermitteln - Entwickler-Forum

Suchst Du select extract(‘epoch’ from now()); ?

For das now() kannst Du halt Deine Zeit angeben.


Andreas

In etwa so? (wenn ich z.b: 15 Minütlich gruppieren will):

SELECT extract(‘epoch’ from St_TimeStamp) * INTERVAL ‘900 second’
AS timeslice, …
GROUP BY timeslice

Ich erhalte dann Werte für timeslice wie:

323231445:00:00

Diese muss ich dann wahrscheinlich selbst in ein Standard-Date-Time-Format umwandeln!?

select extract(‘epoch’ from ‘2010-12-21 13:45:00’::timestamp)::int / (15 * 60);

liefert die Anzahl der 15-Minuten-Blöcke seit EPOCH.

Andreas

Sorry, bin ein vollkommener Postgree-Newbie, aber

select extract(‘epoch’ from ‘2010-12-21 13:45:00’::timestamp)::int / (15 * 60);

ist ein gültiges Postgree-SQL-Statement? Da bekomm ich massenweise Fehler.
Meine Aufgabenstellung ist es, eine Spalte auf Grund eines in der Tabelle vorhandenen Timestamps zu gruppieren → d.h. z.B. viertelstündlich aufsummieren von Statistikwerten.

Was für Fehler?

Hier läuft es:

test=*# select now() + s * '10 minutes'::interval as zeitpunkt, extract('epoch' from (now() + s * '10 minutes'::interval))::int / (15 * 60) as _virtelstunden_nach_epoch from generate_series(1,10) s;
           zeitpunkt           | _virtelstunden_nach_epoch
-------------------------------+---------------------------
 2010-12-21 14:34:51.660856+01 |                   1436598
 2010-12-21 14:44:51.660856+01 |                   1436598
 2010-12-21 14:54:51.660856+01 |                   1436599
 2010-12-21 15:04:51.660856+01 |                   1436600
 2010-12-21 15:14:51.660856+01 |                   1436600
 2010-12-21 15:24:51.660856+01 |                   1436601
 2010-12-21 15:34:51.660856+01 |                   1436602
 2010-12-21 15:44:51.660856+01 |                   1436602
 2010-12-21 15:54:51.660856+01 |                   1436603
 2010-12-21 16:04:51.660856+01 |                   1436604
(10 rows)

In der 2. Spalte steht das, was Du suchst. Die erste Spalte ist nur zur Kontrolle und für die händische Kontrollrechnung …

Andreas

SELECT to_timestamp(extract (‘epoch’ from St_TimeStamp))
AS timeslice,
sum(St_Objects_Pass), sum(St_Objects_Rej_1), sum(St_Objects_Rej_2), sum(St_Sort_Weight_Pass), sum(St_Sort_Weight_Rej_1), sum(St_Sort_Weight_Rej_2), sum(St_Valve_Act_Rej_1), sum(St_Valve_Act_Rej_2), sum(St_NE_Objects), sum(St_Cornsize_25), sum(St_Cornsize_Median), sum(St_Cornsize_75), sum(St_Object_Class_0), sum(St_Object_Class_2), sum(St_Object_Class_3), sum(St_Object_Class_4), sum(St_Object_Class_5), sum(St_Object_Class_6), sum(St_Object_Class_7), sum(St_Object_Class_8), sum(St_Object_Class_9), sum(St_Object_Class_10), sum(St_Object_Class_11), sum(St_Object_Class_12), sum(St_Object_Class_13), sum(St_Object_Class_14), sum(St_Object_Class_15)
FROM statistic
WHERE Ma_ID=4 AND Si_ID=1 AND St_TimeStamp >‘2010-12-20 13:07:29’ AND St_TimeStamp<‘2010-12-22 13:07:29’
GROUP BY timeslice order by timeslice;

So sieht mein komplettes Statement derzeit aus. Liefert mir nun

timeslice sum sum_1 sum_2 sum_3 sum_4 sum_5 sum_6 sum_7 sum_8 sum_9 sum_10 sum_11 sum_12 sum_13 sum_14 sum_15 sum_16 sum_17 sum_18 sum_19 sum_20 sum_21 sum_22 sum_23 sum_24 sum_25 sum_26
2010-12-21 11:02:00+01 13569 19554 14325 451 1375 1212 16021 12877 0 7 8 9 52 1967 209 4383 2730 367 131 0 0 157 0 0 0 0 0
2010-12-21 11:03:00+01 13588 19562 14332 451 1374 1213 16042 12883 0 7 8 9 52 1969 209 4382 2729 367 130 0 0 157 0 0 0 0 0
2010-12-21 11:04:00+01 13582 19576 14330 452 1377 1213 16038 12861 0 7 8 9 52 1968 209 4384 2728 367 131 0 0 157 0 0 0 0 0
2010-12-21 11:05:00+01 13582 19546 14329 451 1376 1212 16024 12872 0 7 8 9 52 1969 210 4381 2731 367 130 0 0 157 0 0 0 0 0
2010-12-21 11:06:00+01 13584 19573 14326 452 1376 1212 16045 12873 0 7 8 9 52 1968 210 4384 2728 367 131 0 0 157 0 0 0 0 0
2010-12-21 13:33:00+01 13582 19542 14329 450 1375 1210 16025 12874 0 7 8 9 52 1969 210 4380 2731 367 130 0 0 157 0 0 0 0 0
2010-12-21 13:34:00+01 13584 19577 14329 451 1373 1212 16025 12870 0 7 8 9 52 1968 210 4384 2728 367 131 0 0 157 0 0 0 0 0
2010-12-21 13:35:00+01 13571 19554 14328 451 1375 1212 16046 12873 0 7 8 9 52 1967 209 4383 2730 367 131 0 0 157 0 0 0 0 0
2010-12-21 13:36:00+01 13588 19562 14332 451 1376 1211 16029 12861 0 7 8 9 52 1969 209 4382 2729 367 130 0 0 157 0 0 0 0 0
2010-12-21 13:37:00+01 13582 19576 14327 450 1375 1211 16028 12870 0 7 8 9 52 1968 209 4384 2728 367 131 0 0 157 0 0 0 0 0
2010-12-21 13:38:00+01 13582 19546 14332 451 1374 1212 16026 12871 0 7 8 9 52 1969 210 4381 2731 367 130 0 0 157 0 0 0 0 0
2010-12-21 13:39:00+01 13584 19573 14326 452 1375 1212 16048 12883 0 7 8 9 52 1968 210 4384 2728 367 131 0 0 157 0 0 0 0 0
2010-12-21 13:40:00+01 13571 19554 14328 452 1377 1213 16043 12865 0 7 8 9 52 1967 209 4383 2730 367 131 0 0 157 0 0 0 0 0
2010-12-21 13:41:00+01 13588 19562 14332 452 1375 1211 16017 12884 0 7 8 9 52 1969 209 4382 2729 367 130 0 0 157 0 0 0 0 0
2010-12-21 13:42:00+01 13582 19576 14330 453 1377 1213 16040 12872 0 7 8 9 52 1968 209 4384 2728 367 131 0 0 157 0 0 0 0 0

Wie kann ich nun auf einen Zeitbereich von x-Minuten gruppieren?

Ich habe da wohl in meinem SQL-Editor eine Option “Allow using of parameters in query text” aktiviert, deswegen funktionierte das Statement bei mir nicht.

Hab das Ganze nun auf

SELECT extract (‘epoch’ from St_TimeStamp)::int / (15 * 60)
AS timeslice, sum(St_Objects_Pass), …
FROM statistic
WHERE Ma_ID=4 AND Si_ID=1 AND St_TimeStamp >‘2010-12-20 13:07:29’ AND St_TimeStamp<‘2010-12-22 13:07:29’
GROUP BY timeslice order by timeslice;

umgebaut. Funktioniert soweit, nur für timeslice erhalte ich einen Wert (z.B.: 1.436.584), mit dem ich nichts anfangen kann, auch wenn ich den wert mittels to_timestamp versuche, umzuwandeln erhalte ich keinen sinnvollen Wert.

Danke schon mal für deine Geduld, ist nicht einfach mit mir :wink:

Edit: Ich bräuchte das Ganze in folgender Form:

z.B. Tabelleneinträge:

Timestamp Wert
15:06 8
15:10 2
15:11 3
15:16 7
15:24 9
15:25 11
15:32 6
15:41 7
15:42 8

Ergebnis (bei 15 Minuten-Gruppierung):

Timestamp Wert
15:15 13
15:30 27
15:45 21

Ich glaub ich habs nun:

SELECT to_timestamp(extract (‘epoch’ from St_TimeStamp)::int / (600) * 600)