Vybrat každý druhý víkend / vypočítat délku výpadku

Dobrý večer,

mám tu dva dotazy, se kterými si nevím rady, a tak doufám, že se najde někdo tak hodný a pomůže mi :)

1. Vybrat každý druhý víkend
V tabulce mám uložené datum ve formátu %Y-%m-%d %H:%i:%s (tedy např. 2011-12-04 21:25:00) a potřebuji získat údaje pro každou druhou sobotu od zadaného data, a to ještě navíc od 20.00 do 22.00 a také pro každou druhou neděli mezi 16.00 a 18.00 a dále mezi 20.00 a 22.00. Snažil jsem se zorientovat v MySQL Date and Time funkcích, ale moudrý z toho nejsem.

2. Zjistit délku výpadku
Jedná se o podobnou funkci, jakou má například monitoring hostingů. Potřebuji pro každý výpadek zjistit od kdy do kdy trval a také vypočítat dobu jeho trvání. Co mi tady ale nejde do hlavy, je, jak zjistit, kdy onen výpadek začal a kdy končí. Výpadek je charakterizován tím, že sloupec server_status nabývá hodnoty 0.

Struktura tabulky:

Kód:

CREATE TABLE IF NOT EXISTS `og_online_count` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `server_name` varchar(255) NOT NULL,
  `server_status` tinyint(1) NOT NULL,
  `online_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=96709

;

Díky za případnou pomoc a pokud jsem se zase vyjádřil jako Mongol a není z toho jasné, co chci, tak upřesním.

ad 1:

Kód:

WHERE ((WEEKDAY(datetime)= 6  -- sobota
   AND HOUR(datetime) BETWEEN 20 AND 22)
OR 
(WEEKDAY(datetime)= 0  -- nedele
   AND (HOUR(datetime) BETWEEN 16 AND 18
      OR HOUR(datetime) BETWEEN 20 AND 22))
)
AND WEEK(datetime)%2 = 0 -- kazdy sudy tyden
AND datetime >= :zadane_datum

---------- Post added 4.12.2011 at 22:52 ----------

ad 2
asi bude lepší to nenechávat je na databázi, ale udělat to v programu.
Tedy, v zadaném období si zjistit, jaké servery měly výpadkek (SELECT DISTINCT server_name FROM og_online_count WHERE server_status = 'down' AND datetime BETWEEN :zacatek AND :konec).

Potom pro každý server zvlášť si vybrat ty řádky kdy byl ve vpýadku, s tím, že začátek se pozná tak, že předchozí "meření" bylo ok, a současné už není.

Paráda! Díky moc. Budu ještě muset zjistit, jestli chci sudý, nebo lichý týden. Tohle řešení mě vůbec nenapadlo, myslel jsem, že tam bude nějak figurovat přičítání 14 dnů :) Ještě jednou díky a dávám +1 :)

OK, díky i za to druhé řešení, pokusím se to promyslet, zítra se na to vrhnu :)

Pokud to zavisi na zadanem datu, tak to bude vypadat bud jako

Kód:

WEEK(datetime)%2 = WEEK(:zadane_datum)%2

nebo

Kód:

WEEK(datetime)%2 = (1+WEEK(:zadane_datum))%2

Zkusím sem nahodit řešení druhého dotazu. Nevím, jak moc vhodná tato konstrukce je, nicméně funguje. Možná jde nějak zjednodušit.

Kód:

SELECT *, TIMEDIFF(datetime_end, datetime_start) dur

FROM
(
    SELECT 
        oc.server_name, 
        oc.datetime datetime_start, 
        (SELECT datetime FROM og_online_count WHERE server_name=oc.server_name AND server_status=1 AND datetime > oc.datetime ORDER BY datetime ASC LIMIT 1) datetime_end

    FROM
    (
        SELECT 
            oc.id,
           (SELECT id FROM og_online_count WHERE datetime < oc.datetime AND server_name=oc.server_name ORDER BY datetime DESC LIMIT 1) p_id
        FROM og_online_count oc
    ) t

    INNER JOIN og_online_count oc ON oc.id=t.id
    LEFT JOIN og_online_count poc ON poc.id=t.p_id

    WHERE oc.server_status=0 AND poc.server_status=1
) t

Teorie je jednoduchá -> vybereme takové záznamy, které splňují server_status == 0, přičemž u předchozího záznamu je server_status == 1, tj. jedná se o záznamy oznamující počátek výpadku. K tomuto řádku pak vybereme nejbližší budoucí datum, u kterého je server_status == 1, tj. konec výpadku. Z těchto dvou hodnot pak už není problém vypočítat délku výpadku.