379 Stimmen

Wiederkehrende Ereignisse im Kalender - beste Speichermethode

Ich baue ein benutzerdefiniertes Ereignissystem, und wenn Sie ein sich wiederholendes Ereignis haben, das wie folgt aussieht:

Die Veranstaltung A wird ab dem 3. März 2011 alle 4 Tage wiederholt.

ou

Veranstaltung B wird ab dem 1. März 2011 alle 2 Wochen am Dienstag wiederholt.

Wie kann ich diese Daten in einer Datenbank so speichern, dass sie einfach abrufbar sind? Ich möchte keine Leistungsprobleme, wenn es eine große Anzahl von Ereignissen gibt, und ich muss durch jede einzelne gehen, wenn der Kalender gerendert wird.

244voto

Brandon Wamboldt Punkte 15689

Speichern von "einfachen" Wiederholungsmustern

Für meinen PHP/MySQL-basierten Kalender wollte ich wiederkehrende Ereignisinformationen so effizient wie möglich speichern. Ich wollte keine große Anzahl von Zeilen haben, und ich wollte alle Ereignisse, die an einem bestimmten Datum stattfinden, leicht nachschlagen können.

Die nachstehende Methode eignet sich hervorragend für die Speicherung sich wiederholender Informationen, die in regelmäßigen Abständen auftreten, z. B. jeden Tag, jedes n Tage, jede Woche, jeden Monat, jedes Jahr, usw. usw. Dazu gehören auch die Muster "Jeden Dienstag" und "Jeden Donnerstag", da sie separat als "Jede Woche, die an einem Dienstag beginnt" und "Jede Woche, die an einem Donnerstag beginnt" gespeichert werden.

Angenommen, ich habe zwei Tabellen, eine mit dem Namen events wie diese:

ID    NAME
1     Sample Event
2     Another Event

Und eine Tabelle namens events_meta wie diese:

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000
2     1             repeat_interval_1  432000

Dabei ist repeat_start ein Datum ohne Zeitangabe als Unix-Zeitstempel und repeat_interval ein Wert in Sekunden zwischen den Intervallen (432000 ist 5 Tage).

repeat_interval_1 geht mit repeat_start der ID 1. Wenn ich also ein Ereignis habe, das sich jeden Dienstag und jeden Donnerstag wiederholt, wäre das repeat_interval 604800 (7 Tage), und es gäbe 2 repeat_starts und 2 repeat_intervals. Die Tabelle würde wie folgt aussehen:

ID    event_id      meta_key           meta_value
1     1             repeat_start       1298959200 -- This is for the Tuesday repeat
2     1             repeat_interval_1  604800
3     1             repeat_start       1299132000 -- This is for the Thursday repeat
4     1             repeat_interval_3  604800
5     2             repeat_start       1299132000
6     2             repeat_interval_5  1          -- Using 1 as a value gives us an event that only happens once

Wenn Sie dann einen Kalender haben, der jeden Tag eine Schleife durchläuft und die Ereignisse des jeweiligen Tages erfasst, würde die Abfrage wie folgt aussehen:

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.`meta_value` )
          END
        ) / EM2.`meta_value`
    ) = 1
LIMIT 0 , 30

Ersetzen von {current_timestamp} mit dem Unix-Zeitstempel für das aktuelle Datum (abzüglich der Uhrzeit, so dass die Werte für Stunde, Minute und Sekunde auf 0 gesetzt werden).

Hoffentlich hilft das auch jemand anderem!


Speicherung "komplexer" sich wiederholender Patterns

Diese Methode ist besser geeignet für die Speicherung komplexer Muster wie

Event A repeats every month on the 3rd of the month starting on March 3, 2011

ou

Event A repeats Friday of the 2nd week of the month starting on March 11, 2011

Ich würde empfehlen, dies mit dem oben genannten System zu kombinieren, um die größtmögliche Flexibilität zu erreichen. Die Tabellen für diese sollte wie:

ID    NAME
1     Sample Event
2     Another Event

Und eine Tabelle namens events_meta wie diese:

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000 -- March 3rd, 2011
2     1             repeat_year_1      *
3     1             repeat_month_1     *
4     1             repeat_week_im_1   2
5     1             repeat_weekday_1   6

repeat_week_im steht für die Woche des aktuellen Monats, die potenziell zwischen 1 und 5 liegen kann. repeat_weekday im Wochentag 1-7.

Wenn Sie nun die Tage/Wochen in einer Schleife durchgehen, um eine Monatsansicht in Ihrem Kalender zu erstellen, könnten Sie eine Abfrage wie folgt zusammenstellen:

SELECT EV . *
FROM `events` AS EV
JOIN `events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key = 'repeat_start'
LEFT JOIN `events_meta` EM2 ON EM2.meta_key = CONCAT( 'repeat_year_', EM1.id )
LEFT JOIN `events_meta` EM3 ON EM3.meta_key = CONCAT( 'repeat_month_', EM1.id )
LEFT JOIN `events_meta` EM4 ON EM4.meta_key = CONCAT( 'repeat_week_im_', EM1.id )
LEFT JOIN `events_meta` EM5 ON EM5.meta_key = CONCAT( 'repeat_weekday_', EM1.id )
WHERE (
  EM2.meta_value =2011
  OR EM2.meta_value = '*'
)
AND (
  EM3.meta_value =4
  OR EM3.meta_value = '*'
)
AND (
  EM4.meta_value =2
  OR EM4.meta_value = '*'
)
AND (
  EM5.meta_value =6
  OR EM5.meta_value = '*'
)
AND EM1.meta_value >= {current_timestamp}
LIMIT 0 , 30

In Kombination mit der obigen Methode könnten die meisten sich wiederholenden/wiederkehrenden Ereignismuster abgedeckt werden. Wenn ich etwas übersehen habe, hinterlassen Sie bitte einen Kommentar.

208voto

ahoffner Punkte 3117

Obwohl die derzeit akzeptierte Antwort eine große Hilfe für mich war, wollte ich einige nützliche Änderungen mitteilen, die die Abfragen vereinfachen und auch die Leistung erhöhen.


"Einfache" Wiederholungsereignisse

Um Ereignisse zu behandeln, die in regelmäßigen Abständen wiederkehren, wie z. B.:

Repeat every other day 

ou

Repeat every week on Tuesday 

Sie sollten zwei Tabellen erstellen, eine mit dem Namen events wie diese:

ID    NAME
1     Sample Event
2     Another Event

Und eine Tabelle namens events_meta wie diese:

ID    event_id      repeat_start       repeat_interval
1     1             1369008000         604800            -- Repeats every Monday after May 20th 2013
1     1             1369008000         604800            -- Also repeats every Friday after May 20th 2013

Mit repeat_start ein Unix-Zeitstempel-Datum ohne Uhrzeit (1369008000 entspricht dem 20. Mai 2013) und repeat_interval eine Zeitspanne in Sekunden zwischen den Intervallen (604800 ist 7 Tage).

Durch eine Schleife über jeden Tag im Kalender können Sie mit dieser einfachen Abfrage wiederkehrende Ereignisse ermitteln:

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE  (( 1299736800 - repeat_start) % repeat_interval = 0 )

Geben Sie einfach den Unix-Zeitstempel (1299736800) für jedes Datum in Ihrem Kalender ein.

Beachten Sie die Verwendung des Moduls (%-Zeichen). Dieses Symbol entspricht einer normalen Division, gibt aber den "Rest" statt des Quotienten zurück und ist somit immer dann 0, wenn das aktuelle Datum ein exaktes Vielfaches des repeat_interval vom repeat_start ist.

Leistungsvergleich

Dies ist wesentlich schneller als die zuvor vorgeschlagene Antwort auf der Grundlage von "meta_keys", die wie folgt lautete:

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.`meta_value` )
          END
        ) / EM2.`meta_value`
    ) = 1

Wenn Sie diese Abfrage mit EXPLAIN ausführen, werden Sie feststellen, dass sie die Verwendung eines Join-Puffers erfordert:

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                          |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
|  1 | SIMPLE      | EM1   | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | Using where                    |
|  1 | SIMPLE      | EV    | eq_ref | PRIMARY       | PRIMARY | 4       | bcs.EM1.event_id |    1 |                                |
|  1 | SIMPLE      | EM2   | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | Using where; Using join buffer |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+

Die obige Lösung mit 1 Verbindung erfordert keinen solchen Puffer.


"Komplexe" Patterns

Sie können die Unterstützung für komplexere Typen hinzufügen, um diese Arten von Wiederholungsregeln zu unterstützen:

Event A repeats every month on the 3rd of the month starting on March 3, 2011

ou

Event A repeats second Friday of the month starting on March 11, 2011

Ihr Veranstaltungstisch kann genau so aussehen:

ID    NAME
1     Sample Event
2     Another Event

Um diese komplexen Regeln zu unterstützen, fügen Sie Spalten zu events_meta etwa so:

ID    event_id      repeat_start       repeat_interval    repeat_year    repeat_month    repeat_day    repeat_week    repeat_weekday
1     1             1369008000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Monday after May 20, 2013
1     1             1368144000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Friday after May 10, 2013
2     2             1369008000         NULL               2013           *               *             2              5                -- Repeats on Friday of the 2nd week in every month    

Beachten Sie, dass Sie lediglich entweder eine repeat_interval ou eine Reihe von repeat_year , repeat_month , repeat_day , repeat_week y repeat_weekday Daten.

Das macht die Auswahl beider Typen gleichzeitig sehr einfach. Gehen Sie einfach in einer Schleife durch jeden Tag und geben Sie die richtigen Werte ein (1370563200 für den 7. Juni 2013, und dann das Jahr, den Monat, den Tag, die Wochennummer und den Wochentag wie folgt):

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE  (( 1370563200 - repeat_start) % repeat_interval = 0 )
  OR ( 
    (repeat_year = 2013 OR repeat_year = '*' )
    AND
    (repeat_month = 6 OR repeat_month = '*' )
    AND
    (repeat_day = 7 OR repeat_day = '*' )
    AND
    (repeat_week = 2 OR repeat_week = '*' )
    AND
    (repeat_weekday = 5 OR repeat_weekday = '*' )
    AND repeat_start <= 1370563200
  )

Hier werden alle Ereignisse angezeigt, die sich am Freitag der 2. Woche wiederholen, wie auch alle Ereignisse, die sich jeden Freitag wiederholen, so dass sowohl Ereignis-ID 1 als auch 2 zurückgegeben werden:

ID    NAME
1     Sample Event
2     Another Event

*Anmerkung: In der obigen SQL habe ich PHP-Datum's Standard-Wochentagsindizes, also "5" für Freitag


Ich hoffe, das hilft anderen so sehr, wie mir die ursprüngliche Antwort geholfen hat!

34voto

user3781087 Punkte 409

Verbesserung: Zeitstempel durch Datum ersetzen

Als kleine Verbesserung der akzeptierten Antwort, die anschließend von ahoffner verfeinert wurde, ist es möglich, ein Datumsformat anstelle eines Zeitstempels zu verwenden. Die Vorteile sind:

  1. lesbare Daten in der Datenbank
  2. kein Problem mit den Jahren > 2038 und dem Zeitstempel
  3. Bei Zeitstempeln, die auf saisonal angepassten Daten beruhen, ist Vorsicht geboten. So beginnt der 28. Juni im Vereinigten Königreich eine Stunde früher als der 28. Dezember, so dass die Ableitung eines Zeitstempels aus einem Datum den Rekursionsalgorithmus unterbrechen kann.

zu tun, ändern Sie den DB repeat_start als Typ 'Datum' zu speichern und repeat_interval hält nun Tage statt Sekunden fest, d.h. 7 für eine Wiederholung von 7 Tagen.

die sql-Zeile ändern:

WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )

zu:

WHERE ( DATEDIFF( '2013-6-7', repeat_start ) % repeat_interval = 0)

alles andere bleibt gleich. Ganz einfach!

32voto

Gal Bracha Punkte 16362

Ich würde diesem Leitfaden folgen: https://github.com/bmoeskau/Extensible/blob/master/recurrence-overview.md

Stellen Sie außerdem sicher, dass Sie das iCal-Format verwenden, damit Sie das Rad nicht neu erfinden und denken Sie an Regel #0: Speichern Sie NICHT einzelne wiederkehrende Ereignisse als Zeilen in Ihrer Datenbank!

27voto

Alex Punkte 411

Für alle, die sich dafür interessieren, können Sie jetzt einfach kopieren und einfügen, um innerhalb weniger Minuten loszulegen. Ich habe die Ratschläge in den Kommentaren so gut wie möglich befolgt. Lasst mich wissen, wenn ich etwas übersehen habe.

"KOMPLEXE VERSION":

Veranstaltungen

+----------+----------------+
| ID       | NAME           | 
+----------+----------------+
| 1        | Sample event 1 |
| 2        | Second  event  |
| 3        | Third event    |
+----------+----------------+

ereignisse_meta

+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
| ID | event\_id | repeat\_start | repeat\_interval  | repeat\_year | repeat\_month | repeat\_day | repeat\_week | repeat\_weekday |
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
| 1  | 1        | 2014-07-04   | 7                | NULL        | NULL         | NULL       | NULL        | NULL           |
| 2  | 2        | 2014-06-26   | NULL             | 2014        | \*            | \*          | 2           | 5              |
| 3  | 3        | 2014-07-04   | NULL             | \*           | \*            | \*          | \*           | 5              |
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+

SQL-Code:

CREATE TABLE IF NOT EXISTS `events` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`ID`, `NAME`) VALUES
(1, 'Sample event'),
(2, 'Another event'),
(3, 'Third event...');

CREATE TABLE IF NOT EXISTS `events_meta` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `repeat_start` date NOT NULL,
  `repeat_interval` varchar(255) NOT NULL,
  `repeat_year` varchar(255) NOT NULL,
  `repeat_month` varchar(255) NOT NULL,
  `repeat_day` varchar(255) NOT NULL,
  `repeat_week` varchar(255) NOT NULL,
  `repeat_weekday` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `events_meta`
--

INSERT INTO `events_meta` (`ID`, `event_id`, `repeat_start`, `repeat_interval`, `repeat_year`, `repeat_month`, `repeat_day`, `repeat_week`, `repeat_weekday`) VALUES
(1, 1, '2014-07-04', '7', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL'),
(2, 2, '2014-06-26', 'NULL', '2014', '*', '*', '2', '5'),
(3, 3, '2014-07-04', 'NULL', '*', '*', '*', '*', '1');

auch erhältlich als MySQL-Export (für leichten Zugang)

PHP-Beispielcode index.php:

<?php
    require 'connect.php';    

    $now = strtotime("yesterday");

    $pushToFirst = -11;
    for($i = $pushToFirst; $i < $pushToFirst+30; $i++)
    {
        $now = strtotime("+".$i." day");
        $year = date("Y", $now);
        $month = date("m", $now);
        $day = date("d", $now);
        $nowString = $year . "-" . $month . "-" . $day;
        $week = (int) ((date('d', $now) - 1) / 7) + 1;
        $weekday = date("N", $now);

        echo $nowString . "<br />";
        echo $week . " " . $weekday . "<br />";

        $sql = "SELECT EV.*
                FROM `events` EV
                RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
                WHERE ( DATEDIFF( '$nowString', repeat_start ) % repeat_interval = 0 )
                OR ( 
                    (repeat_year = $year OR repeat_year = '*' )
                    AND
                    (repeat_month = $month OR repeat_month = '*' )
                    AND
                    (repeat_day = $day OR repeat_day = '*' )
                    AND
                    (repeat_week = $week OR repeat_week = '*' )
                    AND
                    (repeat_weekday = $weekday OR repeat_weekday = '*' )
                    AND repeat_start <= DATE('$nowString')
                )";
        foreach ($dbConnect->query($sql) as $row) {
            print $row['ID'] . "\t";
            print $row['NAME'] . "<br />";
        }

        echo "<br /><br /><br />";
    }
?>

PHP-Beispielcode connect.php:

<?
// ----------------------------------------------------------------------------------------------------
//                                       Connecting to database
// ----------------------------------------------------------------------------------------------------
// Database variables
$username = "";
$password = "";
$hostname = ""; 
$database = ""; 

// Try to connect to database and set charset to UTF8
try {
    $dbConnect = new PDO("mysql:host=$hostname;dbname=$database;charset=utf8", $username, $password);
    $dbConnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
// ----------------------------------------------------------------------------------------------------
//                                      / Connecting to database
// ----------------------------------------------------------------------------------------------------
?>

Auch der php-Code ist hier verfügbar (zur besseren Lesbarkeit):
index.php
und
verbinden.php
Die Einrichtung sollte nur wenige Minuten dauern. Nicht Stunden. :)

CodeJaeger.com

CodeJaeger ist eine Gemeinschaft für Programmierer, die täglich Hilfe erhalten..
Wir haben viele Inhalte, und Sie können auch Ihre eigenen Fragen stellen oder die Fragen anderer Leute lösen.

Powered by:

X