juppix: Mysql: Tage zwischen 2 Datumsangaben auflisten

Hallo zusammen,

ich habe eine Tabelle ("items"), darin stehen Termine:

Id | Tag | Text
------------------------0  | 2018-05-18 10:10:00 | Testtermin
1  | 2018-05-18 22:19:40 | Testtermin 2
2  | 2019-02-19 01:08:10| Testxyz
…

Nun möchte ich per MySql-Query

  1. erst einmal zwischen 2 Daten (bei mir 1 Jahr) alle möglichen Datumswerte auflisten lassen. und
  2. diese dann noch mit der SUMME aller Termine (wenn überhaupt vorhanden am entsprechenden Tag) darstellen lassen.

Also das gewünschte Ergebnis der Abfrage (Zeitraum: 2018-01-01 00:00:00 bis einschl. 2018-12-31 23:59:59) sollte ergeben:

Tag | Summe
----------------------------
2018-01-01 | 0
2018-01-02 | 0
2018-01-03 | 02018-05-18 | 22018-12-30 | 0
2018-12-31 | 0

Folgenden Schnipsel habe ich gegoogelt, aber irgendwie bringt der mich nicht wirklich weiter da er immer nur ein paar Tage auflistet und nicht das ganze Jahr (kompletter Zeitraum) - wieso auch immer (??):

set @i = -1;
SELECT 
  DATE(ADDDATE('2018-01-01', INTERVAL @i:=@i+1 DAY)) AS date,
  (SELECT COUNT(*) FROM items WHERE day = DATETIME(ADDDATE('2018-01-01 00:00:00', INTERVAL @i:=@i+1 DAY))) AS summe 
  
  
  FROM `items`
  /* WHERE YEAR(day) = 2018 */
HAVING 
@i < DATEDIFF('2018-12-31', '2018-01-01')

1000 Dank vorab für Eure Hilfe 😀 jupix

akzeptierte Antworten

  1. Lieber juppix,

    1. erst einmal zwischen 2 Daten (bei mir 1 Jahr) alle möglichen Datumswerte auflisten lassen.

    Das bedeutet ein SELECT, bei dem Du das Datum mit Grenzwerten definierst:

    SELECT *
    FROM `items`
    WHERE `Tag` >= '2018-01-01 00:00:00'
    AND `Tag` <= '2018-12-31 23:59:59';
    

    Das geht auch mit BETWEEN:

    SELECT *
    FROM `items`
    WHERE `Tag` BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59';
    

    und 2. diese dann noch mit der SUMME aller Termine (wenn überhaupt vorhanden am entsprechenden Tag) darstellen lassen.

    Dazu kenne ich keine Lösung. Für alle items im oben definierten Zeitraum kannst Du ja COUNT() einsetzen, um die Gesamtanzahl zu ermitteln. Das ganze jedoch nach Tagen gezählt... hmm.

    Tag | Summe
    ----------------------------
    2018-01-01 | 0
    2018-01-02 | 0
    2018-01-03 | 02018-05-18 | 22018-12-30 | 0
    2018-12-31 | 0
    

    Das bedeutet, dass Du auch für Nicht-Treffer einen Eintrag möchtest. Diese müsste die Abfrage erst erzeugen. Warum soll das schon in der Datenbank geschehen? Wäre es für Dich nicht schneller und verständlicher erreichbar, das mit PHP zu lösen?

    $items = array(
      '2018-01-01' => 0,
      '2018-01-02' => 0,
      '2018-01-03' => 0,
    ...
      '2018-05-18' => 2,
    ...
    );
    

    Du kannst ja meine obige SQL-Abfrage verwenden, um darüber zu iterieren und für jedes gefundene Datum den Wert im passenden Array-Index um eins erhöhen.

    $db_data = array(
      0 => array(
        'id' => 0,
        'Tag' => '2018-05-18 10:10:00',
        'Text' => 'Testtermin'
      ),
      1 => array(
        'id' => 1,
        'Tag' => '2018-05-18 22:19:40',
        'Text' => 'Testtermin2'
      ),
      2 => array(
        'id' => 2,
        'Tag' => '2019-02-19 01:08:10',
        'Text' => 'Testxyz'
      )
    );
    
    $day = '2018-01-01';
    $end = '2019-01-01';
    $items = array();
    
    while ($day != $end) {
    
      // UNIX timestamp for $day
      $time = strtotime($day);
    
      // create array key with counter at zero
      $items[$day] = 0;
    
      // find suitable item from DB
      foreach ($db_data as $item) {
        // item for this day?
        if ($day == date('Y-m-d', strtotime($item['Tag']))) {
          // increment counter by one
          $items[$day]++;
        }
      }
    
      // go to next day
      $day = date('Y-m-d', strtotime('+1 day', $time));
    }
    

    Ich habe das jetzt nicht getestet, aber $items sollte in etwa Deine gewünschte Tabelle ausgeben.

    Liebe Grüße,

    Felix Riesterer.

  2. Hallo juppix,

    ich nehme mal an, dass Du diese Query nicht so gegoogelt hast, sondern Fragmente davon. Und die dann zusammengesetzt hast, ohne sie zu verstehen.

    Eine SQL Query, die Nebenwirkungen auf Variablen hat (also dieses @i:=@i+1), ist kaum beherrschbar. Vor allem dann, wenn die Variable an zwei Stellen aktualisiert wird. Du weißt dann eigentlich nicht, wann was passiert; ich glaube nicht, dass Dir irgendwer garantiert, dass bei einem Select die Spaltenausdrücke immer von links nach rechts ausgewertet werden.

    Die Query funktioniert jedenfalls so, dass sie über die vorhandenen Termine läuft und dafür was zurecht fummelt. Wenn Du 47 Termine hast, bekommst Du 47 Ausgabezeilen. Das willst du nicht. Du willst eine Zeile pro Tag. Eine SQL Funktion, die Dir einen "Bereich" von 01-01-2018 bis 31-12-2018 produziert, gibt es aber meines Wissens nicht.

    Dazu kommt: Pro Zeile wird @i zweimal inkrementiert. Einmal für die date-Spalte, einmal für die summe-Spalte. In undefinierter Reihenfolge. D.h. der Inhalt der date-Spalte bezieht sich auf ein anderes Datum als der Inhalt der summe-Spalte. Das ist Murks.

    Du solltest, wie von Felix angeregt, in der SQL Query nur die Termine im gewünschten Intervall ermitteln. Das aber dann nach Datum gruppieren (GROUP BY) und die Anzahl der Termine pro Tag mit COUNT(*) bestimmen. Ich bin nicht ganz sicher, wie die Datumsspalte in deiner items-Tabelle heißt. Hier habe ich sie mal datum genannt.

    SELECT DATE(datum), COUNT(*)
    FROM items
    WHERE datum BETWEEN  ? AND ?
    GROUP BY DATE(datum)
    ORDER BY DATE(datum)
    

    Ergebnis dieser Query ist eine Row pro Tag, an dem es Termine gibt, mit der Anzahl von Terminen an diesem Tag.

    Diese Query schickst Du von der Programmiersprache deines Vertrauens (JavaScript?! Das hast Du als Tag an den Beitrag gesetzt...) zur Datenbank und sorgst dafür, dass für die beiden Fragezeichen das minimale und maximale gewünschte Datum gebunden wird.

    Du bekommst dann eine Menge mit Tagen zurück, an denen Termine sind. Die liest Du in ein Array ein. Du solltest aber nicht, wie Felix im Testbeispiel, einen numerischen Index verwenden, sondern das Datum als String formatieren und dies als Index verwenden. Das macht es mühsamer, weil Du dann mit jedem Tag im SQL Ergebnis herumsuchen musst. Wie du das Date aus der Query korrekt in einen String übersetzt, musst Du Dir heraussuchen, das habe ich gerade nicht im Kopf.

    Dann kannst Du analog zu Felix' Schleife über den Zeitraum laufen, pro Tag ins Array gucken wieviele Einträge vermerkt sind, und das ausgeben. In meinem Beispiel verwende ich gleich das dd-mm-yyyy Format, das macht es lesbarer.

    $db_data = array(
      '18-05-2018' => 2,
      '06-09-2018' => 7
    );
    
    $day = new DateTime('01-01-2018');
    $end = new DateTime('01-01-2019');
    $oneDay = new DateInterval("P1D");   // 1 Tag
    
    while ($day < $end) {
    
      $dayKey = $day->format("d-m-Y");
      if (isset($db_data[$dayKey]))
          $anz = $db_data[$dayKey];
      else
          $anz = 0;
    
      // oder verkürzt:
      $anz = isset($db_data[$dayKey]) ? $db_data[$dayKey] : 0;
    
      // Ausgabe
      echo "$dayKey: $anz Termin(e)";    // Nur für DEMO
    
      // go to next day
      $day->add($oneDay);
    }
    

    Rolf

    --
    sumpsi - posui - clusi
    1. Boah! Ein dickes WOW und vielen Dank für die ausführlichen Antworten! Ich werde das einmal versuchen und Feedback geben!

      Respekt und Hut ab! 😀

    2. Lieber Rolf,

      Du solltest aber nicht, wie Felix im Testbeispiel, einen numerischen Index verwenden, sondern das Datum als String formatieren und dies als Index verwenden.

      das Ergebnis der Datenbankabfrage hat in aller Regel numerische Indices - je nachdem wie Deine Abfrage-Logik arbeitet. Das ist im Grunde auch egal. Das Iterieren geht in allen Fällen gleichermaßen gut.

      Das macht es mühsamer, weil Du dann mit jedem Tag im SQL Ergebnis herumsuchen musst.

      Dazu gibt es einen passenden Schlüssel Tag (Spaltenname). Wo ist das "mühsam"?

      Wie du das Date aus der Query korrekt in einen String übersetzt, musst Du Dir heraussuchen, das habe ich gerade nicht im Kopf.

      Er verwendet nicht Date, sondern DateTime. Da stehen Uhrzeiten mit dran. Daher verwende ich ja auch strtotime(), um einen UNIX-Timestamp daraus zu erzeugen, mit dem man dann wieder besser rechnen kann.

      Dann kannst Du analog zu Felix' Schleife über den Zeitraum laufen, pro Tag ins Array gucken wieviele Einträge vermerkt sind, und das ausgeben.

      Zugegeben. Die Zählung würde in Deinem Beispiel von der Datenbank geleistet, in meinem Beispiel von meiner PHP-Logik.

      In meinem Beispiel verwende ich gleich das dd-mm-yyyy Format, das macht es lesbarer.

      Lesbarer? Diese Transformation empfinde ich als sehr störend, da sie dann nicht mehr universell, sondern auf eine bestimmte "Locale" bezogen gilt. Das empfinde ich als "unsauberes Programmieren". Lieber ist mir eine Vorgehensweise, die erst bei der Ausgabe das Datumsformat überhaupt "lesbarer" macht. Für interne Berechnungen will ich YYYY-MM-DD, und zwar unter allen Umständen!

      Liebe Grüße,

      Felix Riesterer.

      1. Hallo Felix,

        das Ergebnis der Datenbankabfrage hat in aller Regel numerische Indices

        Ja, sicher. Aber was ich sagen wollte, ist: Verwende nicht die nackte Datenbankabfrage. Bereite das Ergebnis so auf, dass es zur benötigten Verarbeitung passt. Ein Lookup über einen Hashkey ist schneller und kompakter programmiert als das sequenzielle Durchsuchen eines Array.

        Aber ich will zugeben, dass die Keys eines Lookup-Arrays besser im ISO-Format Ymd dargestellt sind.

        Mit "besser lesbar" meinte ich die Ausgabe. Alles als d-m-Y darzustellen erspart es, für Ausgabe und Lookup getrennt formatieren zu müssen. Aber du hast recht, das ist die Falle „Mikrooptimierung“, in die ich da getappt bin.

        Rolf

        --
        sumpsi - posui - clusi
        1. Hi Rolf,

          ich kriege nach Deinem Beispiel oben nun meine Ergebnisse in folgendem Array...:

          
          Array
          (
              [0] => Array
                  (
                      [2018-06-04] => 1
                  )
          
              [1] => Array
                  (
                      [2018-06-13] => 1
                  )
          
              [2] => Array
                  (
                      [2018-06-16] => 1
                  )
          
              [3] => Array
                  (
                      [2018-06-19] => 3
                  )
          
              [4] => Array
                  (
                      [2018-06-20] => 2
                  )
          
              [5] => Array
                  (
                      [2018-06-21] => 1
                  )
          
              [6] => Array
                  (
                      [2018-06-23] => 11
                  )
          ...
          

          Soweit so gut,

          leider klappt folgende Schleife nicht, die Du angegeben hast.

          Hier mein Code:

          			/* HEATMAP -------------------------------------------------------------- */
          			if(isset($_GET["showHeatmap"])) {
                          $jahr = $_GET["showHeatmap"];  // Hier wird das Jahr übergeben
                          
          				$sql_heatmap = sprintf("
                                              SELECT DATE(items.day), COUNT(*)
                                              FROM items                                    
                                              WHERE YEAR(items.day) = %d 
                                              GROUP BY DATE(items.day)
                                              ORDER BY DATE(items.day) 
          								", $jahr);								
          				$result_heatmap = mysql_query($sql_heatmap) or die(mysql_error());	
                          
                          $HeatmapZeilen = array();
                          while ($row_heatmap = mysql_fetch_array($result_heatmap)) { 
                              $HeatmapZeilen[] = array ( $row_heatmap[0] => $row_heatmap[1] );                    
                          }
                                          
                          
                          //die(print_r($HeatmapZeilen));  // Debug-Ausgabe des Arrays
                          
                          $day = new DateTime('01-01-'.$jahr);
                          $end = new DateTime('01-01-'.($jahr + 1));
                          $oneDay = new DateInterval("P1D");   // 1 Tag
                          
                          while ($day < $end) {                        
                              $dayKey = $day->format("d-m-Y");
                              
                              if (isset($HeatmapZeilen[$dayKey]))     // HIER MUSS DER FEHLER LIEGEN
                                $anz = $HeatmapZeilen[$dayKey];       // HIER MUSS DER FEHLER LIEGEN
                              else
                                $anz = 0;
                              
                              //echo '<hr>'.$dayKey.$anz;
                              
                              // go to next day
                              $day->add($oneDay);
                          }
                      }			
          			/* HEATMAP -------------------------------------------------------------- */
          

          Es wird bei mir immer "0" pro Tag ausgegeben. Also "findet" er das Datum in meinem Array nicht (?). Und woher nimmt er dann die 2. Spalte (Summer der am Tag vorhandenen Termine?).

          Viele Grüße jupix

          1. Ich glaube ich hab's rausgefunden: - 1000 Dank nochmals

                   
                            $HeatmapZeilen = array();
                            while ($row_heatmap = mysql_fetch_array($result_heatmap)) { 
                                //$HeatmapZeilen[] = array ( $row_heatmap[0] => $row_heatmap[1] );                    
                                $HeatmapZeilen[$row_heatmap[0]] = $row_heatmap[1];
                            }
                                            
                            //$HeatmapZeilen = array("test" => "J");
                            //die(print_r($HeatmapZeilen));
                                                        
                            //$res_heatmap = mysql_fetch_array($result_heatmap);
                            
                            
                            $day = new DateTime('01-01-'.$jahr);
                            $end = new DateTime('01-01-'.($jahr + 1));
                            $oneDay = new DateInterval("P1D");   // 1 Tag
                            
                            while ($day < $end) {                        
                                $dayKey = $day->format("Y-m-d");
                                
                                if (isset($HeatmapZeilen[$dayKey])) {
                                  $anz = $HeatmapZeilen[$dayKey]; }
                                else
                                  $anz = 0;
                                
                                //echo '<hr>'.$dayKey.$anz;
                                $out[] = array(  
            						'id' => 1,
            						'name' => 'HEATMAP',
            						'location' => '('.$anz.'x)',
            						'startDate' => $dayKey,
            						'endDate' => $dayKey,
            						'color' => '#ff0000',
                                    'typ' => 'heatmap'
            					);	
                                
                                // go to next day
                                $day->add($oneDay);
                            }
            
            1. Hallo juppix,

              ich war seit Donnerstag nachmittag offline und konnte darum nicht antworten. Gut, dass Du den Formatierfehler selbst gefunden hast 😀

              Ein kleiner A-Bär brummt aber in meinem Kopf herum. Die mysql-Schnittstelle ist seit PHP 5.5. missbilligt (deprecated) und mit PHP 7 nicht mehr Teil von PHP. Das solltest Du zumindest auf mysqli ändern, oder gleich auf PDO umsteigen.

              Vor allem hat der kleine A-Bär noch einen großen Bruder, der soeben sein Maul aufreißt, um Dir die Finger abzubeißen: Du verwendest einen per URL-Parameter übergebenen Wert und überträgst ihn ungeprüft und unmaskiert in dein SQL. Wenn Dir jemand den String ; DROP TABLE items -- übergibt, ist deine Datenbank futsch.

              Grundhaltung des UI-Programmierers soll sein: Der User ist dein Feind und will Dich vernichten. Schütze Dich davor. Prüfe jeden Wert auf Plausibilität. Wenn Du Userdaten irgendwohin überträgst, entferne oder maskiere Zeichen, die im Zielkontext Schaden anrichten können. Denn dein User ist dein Feind und wird diesen Schaden verursachen, wo immer Du ihn lässt. PHP bietet dafür eine Menge Mittel an. Und unser Wiki hat dazu auch was.

              Im vorliegenden Fall reicht ein einfaches intval(), um Angriffe über den Jahreszahl-Parameter zu entschärfen.

              Mit mysqli oder PDO kannst Du auch auf gebundene Parameter umsteigen und musst sie nicht mehr statisch ins SQL einsetzen.

              Rolf

              --
              sumpsi - posui - clusi
              1. Tach!

                Vor allem hat der kleine A-Bär noch einen großen Bruder, der soeben sein Maul aufreißt, um Dir die Finger abzubeißen: Du verwendest einen per URL-Parameter übergebenen Wert und überträgst ihn ungeprüft und unmaskiert in dein SQL. Wenn Dir jemand den String ; DROP TABLE items -- übergibt, ist deine Datenbank futsch.

                Geht nur mit mysqli_multi_query(). Aber andere Arten der SQL-Injection sind auch ohne zweites Statement möglich.

                Grundhaltung des UI-Programmierers soll sein: Der User ist dein Feind und will Dich vernichten. Schütze Dich davor. Prüfe jeden Wert auf Plausibilität. Wenn Du Userdaten irgendwohin überträgst, entferne oder maskiere Zeichen, die im Zielkontext Schaden anrichten können. Denn dein User ist dein Feind und wird diesen Schaden verursachen, wo immer Du ihn lässt. PHP bietet dafür eine Menge Mittel an. Und unser Wiki hat dazu auch was.

                Zu speziell formuliert. Jegliche Daten und nicht nur Usereingaben müssen korrekt weiterverarbeitet werden. Selbst dann, wenn sie höchst vertrauenswürdig als Literal vorliegen.

                dedlfix.