날짜 범위 비교

날짜 범위 비교

MySQL에서 날짜 범위(range-start 및 range-end) 목록이 있는 경우.

10/06/1983 to 14/06/1983
15/07/1983 to 16/07/1983
18/07/1983 to 18/07/1983

그리고 다른 날짜 범위에 이미 목록에 있는 범위가 있는지 확인하고 싶은데, 어떻게 하면 될까요?

예.

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

이것은 고전적인 문제이며 논리를 뒤집으면 실제로 더 쉽습니다.

예를 하나 들어보죠.

여기에 한 기간과 어떤 식으로든 겹치는 다른 기간들의 다양한 변화들을 게시하겠습니다.

           |-------------------|          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

한편, 겹치지 않는 것을 모두 투고합니다.

           |-------------------|          compare to this one
     |---|                                ends before
                                 |---|    starts after

따라서 단순하게 비교를 줄일 수 있습니다.

starts after end
ends before start

겹치지 않는 기간과 겹치지 않는 기간을 모두 찾을 수 있습니다.

마지막 NOT IN LIST 예에서는 두 가지 규칙과 일치함을 알 수 있습니다.

다음 기간이 범위 내인지 범위를 벗어나는지 결정해야 합니다.

           |-------------|
   |-------|                       equal end with start of comparison period
                         |-----|   equal start with end of comparison period

테이블에 range_end 및 range_start라는 열이 있는 경우 일치하는 모든 행을 검색할 수 있는 간단한 SQL을 다음에 나타냅니다.

SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
           OR range_end < @check_period_start)

그 안에 없는 것에 주의해 주세요.두 개의 단순한 규칙이 일치하지 않는 행을 모두 찾기 때문에 단순 NOT는 해당 행을 반전하여 일치하지 않는 행 중 하나가 아닌 경우 일치하는 행 중 하나여야 합니다.

여기서 간단한 반전 논리를 적용하여 NOT를 배제하면 다음과 같은 결과를 얻을 수 있습니다.

SELECT *
FROM periods
WHERE range_start <= @check_period_end
      AND range_end >= @check_period_start

예를 들어 06/06/1983 ~18/06/1983 의 범위에서 start 및 end 라고 불리는 열이 있다고 가정하면 다음과 같은 절을 사용할 수 있습니다.

where ('1983-06-06' <= end) and ('1983-06-18' >= start)

즉, 테스트 범위의 시작이 데이터베이스 범위의 종료 전이고 테스트 범위의 종료가 데이터베이스 범위의 시작 이후인지 또는 시작 시간인지 확인합니다.

사용하시는 RDBMS가 OLAP() 함수를 지원하는 경우, 이것은 사소한 것이 됩니다.자체 개발 솔루션은 필요 없습니다.(Oracle에서는 겉으로는 작동하지만 문서화되어 있지 않습니다).

예상한 결과로는 다음과 같습니다.

06/18/06/198 = 목록 내

단, 이 기간에는 테이블 내의 기간(목록 없음)이 포함되어 있지 않거나 포함되어 있지 않습니다.다만, 그 기간은 1983년 10월 6일부터 1983년 14월 6일까지와 겹칩니다.

Snodgrass 책(http://www.cs.arizona.edu/people/rts/tdbbook.pdf)은 mysql을 미리 실행했지만 시간의 개념은 변하지 않았습니다;-)을 참조할 수 있습니다.

MySQL에서 이 문제에 대처하기 위한 기능을 만들었습니다.사용하기 전에 날짜를 초로 변환하십시오.

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 ;

다음 예를 보겠습니다.당신에게 도움이 될 거예요.

    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

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];

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;

BETWEEN sql 문을 사용하는 다른 방법

포함 기간:

SELECT *
FROM periods
WHERE @check_period_start BETWEEN range_start AND range_end
  AND @check_period_end BETWEEN range_start AND range_end

제외된 기간:

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)

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) )

언급URL : https://stackoverflow.com/questions/143552/comparing-date-ranges