1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

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

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von mark_A17, 3 August 2019.

  1. mark_A17

    mark_A17 Neuer Benutzer

    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
     
  2. akretschmer

    akretschmer Datenbank-Guru

    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 ...
     
  3. mark_A17

    mark_A17 Neuer Benutzer

    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.
     
  4. akretschmer

    akretschmer Datenbank-Guru

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

    Have fun & success!
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden