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

[mark_A17]

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

+++++++++++++++++++++++++++++++++
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

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

 

[akretschmer]

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]

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.

 

[akretschmer]

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.

Na okay. Falls Dir es gelingen sollte, mein Beispiel nach MySQL zu übersetzen, wird das dann ähnlich zu lösen sein.

Have fun & success!