Abfrage mit TIMESTAMPDIFF zwischen 12:00 - 12:00 Uhr

mark_A17

Neuer Benutzer
Beiträge
2
Hallo,
ich habe eine Tabelle mit 160.000 Zeilen. Jede Zeile enthält einen Messwert zu einem bestimmten Zeitpunkt:

Code:
+++++++++++++++++++++++++++++++++
1     2006-01-22T20:21:46    +30
2     2006-01-22T22:34:35    +30
3     2006-01-22T23:42:37    +30
4     2006-01-23T00:42:15    +30
5     2006-01-23T00:57:23    +30
6     2006-01-23T01:17:13    +30
7     2006-01-23T02:22:32    +30
8     2006-01-24T19:26:35    +30
9     2006-01-24T20:01:29    +30
10    2006-01-24T21:12:02    +10
11    2006-01-24T21:12:17    +10
12    2006-01-24T21:12:31    +10
13    2006-01-28T20:10:43    +30
14    2006-01-28T20:22:39    +30
15    2006-01-28T21:33:09    +10
16    2006-01-28T22:17:17    +10
++++++++++++++++++++++++++++++++
Ich möchte eine Übersicht:
1. an wieviel Tagen pro Monat Messungen durchgeführt wurden.
2. Wieviel Zeit zwischen der ersten und letzten Messung je Tag vergangen ist

Aber:
Tages-Abgrenzung soll von 12:00 bis 12:00 sein. Ganz bewusst NICHT von 00:00 bis 24:00 Uhr - siehe Daten vom 22./23.1.2006
Die id's 1-7 gehören zu einem Tag sowie 8-12 und 13-16.

Ergebnis sollte sein:

Summe der Sekunden je Tag
die zwischen der 1. und
letzten Messung an einem
Tag vergangen sind:

2006-01-22 = 21646
2006-01-24 = 6356
2006-01-28 = 7594


Ausserdem hätte ich noch gerne die Summe der einzelnen Tage pro Monat, wobei ein Tag wieder von 12:00 - 12:00 dauern soll.
Also:

Summe der Messtage pro Monat: 2016-01 = 3

Meine bisherige Abfrage
Code:
SELECT DATE_FORMAT(`date_obs`, '%Y-%m-%d') as T_DATE ,TIMESTAMPDIFF(SECOND,MIN(`date_obs`),MAX(`date_obs`)) as T_SESSION
FROM t1
GROUP BY LEFT(`date_obs`,10);
liefert natürlich aber nur


2006-01-22 = 12051
2006-01-23 = 6017
2006-01-24 = 6356
2006-01-28 = 7594

weil die id's 1-3 und 4-7 als zwei getrennte Tage gesehen werden.
siehe:
SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Wie kann ich die Zusammenfassung der Tage in einem Zeitraum von 12:00 - 12:00 erreichen?
Ich benutzt sql nicht so oft und finde einfach nicht den passenden Ansatz.

Grüße

Mark
 
Werbung:

akretschmer

Datenbank-Guru
Beiträge
9.532
Code:
postgres=# select * from bla;
 id |         ts          | val
----+---------------------+-----
  1 | 2006-01-22 20:21:46 |  30
  2 | 2006-01-22 22:34:35 |  30
  3 | 2006-01-22 23:42:37 |  30
  4 | 2006-01-23 00:42:15 |  30
  5 | 2006-01-23 00:57:23 |  30
  6 | 2006-01-23 01:17:13 |  30
  7 | 2006-01-23 02:22:32 |  30
  8 | 2006-01-24 19:26:35 |  30
  9 | 2006-01-24 20:01:29 |  30
 10 | 2006-01-24 21:12:02 |  10
 11 | 2006-01-24 21:12:17 |  10
 12 | 2006-01-24 21:12:31 |  10
 13 | 2006-01-28 20:10:43 |  30
 14 | 2006-01-28 20:22:39 |  30
 15 | 2006-01-28 21:33:09 |  10
 16 | 2006-01-28 22:17:17 |  10
(16 rows)

postgres=# with x as (select *, (ts+'12hours'::interval)::date as tag from bla) select tag, extract(hour from max(ts)-min(ts)) * 3600 + extract(minute from max(ts)-min(ts))*60 + extract(seconds from max(ts)-min(ts)) as sekunden from x group by tag order by tag;
    tag     | sekunden
------------+----------
 2006-01-23 |    21646
 2006-01-25 |     6356
 2006-01-29 |     7594
(3 rows)

postgres=#

Die andere Frage nach "Summe der einzelnen Tage pro Monat" versteh ich grad nicht ...
 

mark_A17

Neuer Benutzer
Beiträge
2
Danke für die Antwort. Ich versuch als Anfänger grad noch den postgres syntax nach mysql zu übersetzen :)
Kann noch dauern.
Im Beispiel ist die Summe der Tage pro Monat "3", weil es an drei verschiedenen Tagen Messungen gibt - und eben nicht 4 wenn im normalen 24h-Intervall gezählt wird.
 
Werbung:
Oben