wykrycie odpowiedniej zmiany oraz różnica między czasem Opcje

[john_doe]

proszę o podpowiedź merytoryczną w dwóch tematach:

1. Mam tabele ze zmianami ( w sensie zmiany w zakładzie pracy), 3 zmiany

shifts
shift_id
shift_name
shift_start
shift_stop

uzupełniona jest tak

1 | shift_1 | 06:00:00 | 14:00:00
1 | shift_2 | 14:00:00 | 22:00:00
1 | shift_3 | 22:00:00 | 06:00:00

na podstawie aktualnego czasu chce wyznaczyć, która jest obecnie zmiana.
zapytanie w stylu

[SQL] pobierz, plaintext 
 SELECT * FROM shifts WHERE <aktualna godzina> BETWEEN HOUR(shift_start) AND HOUR(shift_end);
[SQL] pobierz, plaintext 

nie działa dla 3 zmiany. I w sumie musiałbym zmienić lekko te godziny aby przedziały na siebie nie nachodziły -> czyli lekko rozbudować ten helper o to by patrzył też na minuty ( zmiany musiała by się kończy np. o 13:59:00 )

załatwiam to helperem w CI

[PHP] pobierz, plaintext 
    function current_shift()
    {
        //ensure that there is something in the variable - in case of no match
        $shift_id = '';
 
        $ci =& get_instance();
 
        $current_hour = date("H") + 1;
 
        $ci->db->select('shift_id');
        $ci->db->where($current_hour . ' BETWEEN shift_start AND shift_end', NULL, FALSE);
 
        $query = $ci->db->get('shifts'); 
 
        $result = $query->row(0);
 
        $shift_id = $result->shift_id;
 
 
        return $shift_id;     
    }
[PHP] pobierz, plaintext 

2. Różnica między czasami

załóżmy, że jest 1 zmiana ( czyli między 8 a 14), załóżmy dalej stan aplikacji na 10:30. Potrzebuję wyliczyć ile upłynęło czasu między 10:30 a 8 w sekundach. Ten przypadek jeszcze nie taki zły ale czy w ten sam sposób da się wliczyć czas między np. 23 a 2.30 w nocy? Tutaj też się zmienia data i nie wiem czy to ma znaczenie dla tej kalkulacji?


---
jak widać oba punkty są poniekąd zależne od siebie bo oba bazują na tabeli 'shifts'

Ten post edytował john_doe 13.01.2013, 11:53:51

[sabat24]

1. Osobiście w dynamicznym przykładzie dodałbym 2 dodatkowe kolumny (użyję typu TIME dla lepszej czytelności, ale mógłby to być jakiś int) i znormalizował je do północy i na nich wykonywał operacje szukania oraz pobierania różnicy w czasach. W przypadku dodawania / edycji zmiany, należałoby sprawdzić, czy ma najmniejszą wartość. Jeśli tak, pobrać różnicę między nią i północą i wpisać do dodatkowych kolumn odpowiednie wartości dla danej zmiany pomniejszone o tę różnicę. W przypadku pola TIME daty nie mogłyby zachodzić na siebie, żeby ostatnia zmiana kończyła się o 23:59:59, a nie o 00:00:00. Oczywiście wcześniejsza różnica musiałyby być znana, aby odpowiednio skorygować czas przekazywany do zapytania. Jeśli zmiany Ci się nie zmieniają w szalonym tempie i możesz w pliku konfiguracyjnym przyjąć, że pierwsza zaczyna się o 06:00:00, to uprości sprawę.

W tym wypadku miałbyś więc 2 dodatkowe kolumny: shift_start_normalized, shift_stop_normalized i dane
1 | shift_1 | 06:00:00 | 14:00:00 | 00:00:00 | 07:59:59
2 | shift_2 | 14:00:00 | 22:00:00 | 08:00:00 | 13:59:59
3 | shift_3 | 22:00:00 | 06:00:00 | 14:00:00 | 23:59:59

Do zapytania wrzucasz AKTUALNY_CZAS - RÓŻNICA, czyli Twoje zapytanie wyglądałoby tak:
SELECT * FROM shifts WHERE <aktualna godzina - 6> BETWEEN shift_start_normalized AND shift_stop_normalized

Zakładając, że masz tylko 3 zmiany i raczej się nie zmienia to za często, to można wykorzystać drugi sposób.

2. Pola shift_start i shift_stop są oczywiście typu TIME

[SQL] pobierz, plaintext 
SELECT * FROM shifts WHERE
 (shift_start < shift_stop AND
         shift_start <= '06:00:00' AND
         shift_stop > '06:00:00')
         OR
         (shift_start > shift_stop AND
         ('06:00:00' < shift_stop OR
         '06:00:00' >= shift_start))
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
SELECT * FROM shifts WHERE
 (shift_start < shift_stop AND
         shift_start <= '04:00:00' AND
         shift_stop > '04:00:00')
         OR
         (shift_start > shift_stop AND
         ('04:00:00' < shift_stop OR
         '04:00:00' >= shift_start))
[SQL] pobierz, plaintext 

Oba wyniki zwrócą pierwszą zmianę.

[SQL] pobierz, plaintext 
SELECT * FROM shift WHERE
 
 (shift_start < shift_stop AND
         shift_start <= '14:00:00' AND
         shift_stop > '14:00:00')
         OR
         (shift_start > shift_stop AND
         ('14:00:00' < shift_stop OR
         '14:00:00' >= shift_start))
[SQL] pobierz, plaintext 

Wynik zwróci drugą zmianę.

Przykładowe wartości '06:00:00' i '14:00:00' to aktualna godzina.

Jak już sobie pobierzesz shift_start dla odpowiedniej zmiany, to później niech helper obliczy Ci różnicę czasu między aktualną porą i pobranym początkiem zmiany. Nie ma sensu zaprzęgać do tego MySQL (jeśli koniecznie chcesz, potrzebna będzie instrukcja warunkowa). W przypadku nr 1 wynik dostaniesz jako TIMEDIFF ('aktualna_godzina', shift_start)