Porównywanie zakresów dat
W MySQL, jeśli mam listę zakresów dat (range-start i range-end). np.
10/06/1983 to 14/06/1983
15/07/1983 to 16/07/1983
18/07/1983 to 18/07/1983
I chcę sprawdzić, czy inny zakres dat zawiera którykolwiek z zakresów już na liście, jak mam to zrobić?
Np.
06/06/1983 to 18/06/1983 = IN LIST
10/06/1983 to 11/06/1983 = IN LIST
14/07/1983 to 14/07/1983 = NOT IN LIST
10 answers
Jest to klasyczny problem, a właściwie łatwiej jest odwrócić logikę.
Pozwól, że podam ci przykład.Zamieszczę tutaj jeden okres czasu i wszystkie różne odmiany innych okresów, które w jakiś sposób pokrywają się.
|-------------------| compare to this one
|---------| contained within
|----------| contained within, equal start
|-----------| contained within, equal end
|-------------------| contained within, equal start+end
|------------| not fully contained, overlaps start
|---------------| not fully contained, overlaps end
|-------------------------| overlaps start, bigger
|-----------------------| overlaps end, bigger
|------------------------------| overlaps entire period
Z drugiej strony, pozwolę sobie zamieścić wszystkie te, które się nie nakładają:
|-------------------| compare to this one
|---| ends before
|---| starts after
Więc jeśli po prostu zmniejszysz porównanie do:
starts after end
ends before start
Wtedy znajdziesz wszystkie te, które nie nakładają się na siebie, a potem znajdziesz wszystkie okresy niepasujące.
W ostatnim przykładzie, który nie znajduje się na liście, możesz zobaczyć, że pasuje do tych dwóch zasad.
Będziesz musiał zdecydować, czy następujące okresy są w Twoim przedziale lub poza nim:
|-------------|
|-------| equal end with start of comparison period
|-----| equal start with end of comparison period
Jeśli twoja tabela zawiera kolumny o nazwie range_end i range_start, Oto prosty SQL do pobrania wszystkich pasujących wierszy:
SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
OR range_end < @check_period_start)
Zwróć uwagę na Nie tam. Ponieważ dwie proste reguły znajdują wszystkie niepasujące wiersze , prosta nie odwróci aby powiedzieć: jeśli nie jest jednym z niepasujących wierszy, musi być jednym z pasujących .
Zastosowanie prostej logiki odwrotnej tutaj, aby pozbyć się NOT i skończysz z:
SELECT *
FROM periods
WHERE range_start <= @check_period_end
AND range_end >= @check_period_start
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-05 00:59:48
Biorąc przykładowy zakres od 06/06/1983 do 18/06/1983 i zakładając, że masz kolumny o nazwie start i end dla swoich zakresów, możesz użyć klauzuli takiej jak ta
where ('1983-06-06' <= end) and ('1983-06-18' >= start)
Tzn. sprawdź, czy początek zakresu testowego jest przed końcem zakresu bazy danych, a koniec zakresu testowego jest po lub na początku zakresu bazy danych.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-09-27 12:41:20
Jeśli twój RDBMS obsługuje funkcję OVERLAP (), to staje się to trywialne -- nie ma potrzeby tworzenia własnych rozwiązań. (W wyroczni to działa, ale jest nieudokumentowane).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-04-22 23:43:00
W oczekiwanych wynikach mówisz
06/06/1983 do 18/06/1983 = w liście
Jednak ten okres nie zawiera ani nie jest zawarty przez żaden z okresów w tabeli(nie lista!) okresów. Nakładają się one jednak na okres od 10/06/1983 do 14/06/1983.
Możesz znaleźć książkę Snodgrass ( http://www.cs.arizona.edu/people/rts/tdbbook.pdf ) przydatne: wstępnie datuje mysql, ale pojęcie czasu się nie zmieniło ; -)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-09-29 13:47:03
Stworzyłem funkcję, aby poradzić sobie z tym problemem w MySQL. Wystarczy przekonwertować daty NA sekundy przed użyciem.
DELIMITER ;;
CREATE FUNCTION overlap_interval(x INT,y INT,a INT,b INT)
RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE
overlap_amount INTEGER;
IF (((x <= a) AND (a < y)) OR ((x < b) AND (b <= y)) OR (a < x AND y < b)) THEN
IF (x < a) THEN
IF (y < b) THEN
SET overlap_amount = y - a;
ELSE
SET overlap_amount = b - a;
END IF;
ELSE
IF (y < b) THEN
SET overlap_amount = y - x;
ELSE
SET overlap_amount = b - x;
END IF;
END IF;
ELSE
SET overlap_amount = 0;
END IF;
RETURN overlap_amount;
END ;;
DELIMITER ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-03 03:24:04
Spójrz na poniższy przykład. To ci pomoże.
SELECT DISTINCT RelatedTo,CAST(NotificationContent as nvarchar(max)) as NotificationContent,
ID,
Url,
NotificationPrefix,
NotificationDate
FROM NotificationMaster as nfm
inner join NotificationSettingsSubscriptionLog as nfl on nfm.NotificationDate between nfl.LastSubscribedDate and isnull(nfl.LastUnSubscribedDate,GETDATE())
where ID not in(SELECT NotificationID from removednotificationsmaster where Userid=@userid) and nfl.UserId = @userid and nfl.RelatedSettingColumn = RelatedTo
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-19 05:52:27
Spróbuj tego na MS SQL
WITH date_range (calc_date) AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [ending date]) - DATEDIFF(DAY, [start date], [ending date]), 0)
UNION ALL SELECT DATEADD(DAY, 1, calc_date)
FROM date_range
WHERE DATEADD(DAY, 1, calc_date) <= [ending date])
SELECT P.[fieldstartdate], P.[fieldenddate]
FROM date_range R JOIN [yourBaseTable] P on Convert(date, R.calc_date) BETWEEN convert(date, P.[fieldstartdate]) and convert(date, P.[fieldenddate])
GROUP BY P.[fieldstartdate], P.[fieldenddate];
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-07 08:21:16
CREATE FUNCTION overlap_date(s DATE, e DATE, a DATE, b DATE)
RETURNS BOOLEAN DETERMINISTIC
RETURN s BETWEEN a AND b or e BETWEEN a and b or a BETWEEN s and e;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-05-29 08:05:46
Inna metoda za pomocą polecenia BETWEEN SQL
Okresy włączone:
SELECT *
FROM periods
WHERE @check_period_start BETWEEN range_start AND range_end
AND @check_period_end BETWEEN range_start AND range_end
Okresy wyłączone:
SELECT *
FROM periods
WHERE (@check_period_start NOT BETWEEN range_start AND range_end
OR @check_period_end NOT BETWEEN range_start AND range_end)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-15 12:35:22
SELECT *
FROM tabla a
WHERE ( @Fini <= a.dFechaFin AND @Ffin >= a.dFechaIni )
AND ( (@Fini >= a.dFechaIni AND @Ffin <= a.dFechaFin) OR (@Fini >= a.dFechaIni AND @Ffin >= a.dFechaFin) OR (a.dFechaIni>=@Fini AND a.dFechaFin <=@Ffin) OR
(a.dFechaIni>=@Fini AND a.dFechaFin >=@Ffin) )
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-22 18:27:50