IT

두 날짜 사이의 날짜 목록 가져 오기

lottoking 2020. 9. 14. 21:25
반응형

두 날짜 사이의 날짜 목록 가져 오기

표준 mysql 함수를 사용하면 두 날짜 사이의 날짜 목록을 반환하는 쿼리를 작성하는 방법이 있습니다.

예를 들어 2009-01-01 및 2009-01-13이 주어지면 값이있는 하나의 열 테이블을 반환합니다.

 2009-01-01 
 2009-01-02 
 2009-01-03
 2009-01-04 
 2009-01-05
 2009-01-06
 2009-01-07
 2009-01-08 
 2009-01-09
 2009-01-10
 2009-01-11
 2009-01-12
 2009-01-13

편집 : 명확하지 않은 것입니다. 이 목록을 생성하고 싶습니다. 데이터베이스에 (날짜 시간 기준) 날짜 값이 저장되어 있고 위와 같이 날짜가 저장되어 있습니다.

이 저장 프로 시저를 사용하여 time_intervals 라는 임시 테이블에 필요한 간격을 생성 한 다음 임시 time_intervals 테이블 과 함께 데이터 테이블을 조인하고 구입합니다 .

프로시 저는 지정된 모든 유형의 간격을 생성 할 수 있습니다. 

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals  
.
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
. 
select * from time_intervals
.  
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 00:09:59 
2009-01-01 00:10:00 2009-01-01 00:19:59 
2009-01-01 00:20:00 2009-01-01 00:29:59 
2009-01-01 00:30:00 2009-01-01 00:39:59 
2009-01-01 00:40:00 2009-01-01 00:49:59 
2009-01-01 00:50:00 2009-01-01 00:59:59 
2009-01-01 01:00:00 2009-01-01 01:09:59 
2009-01-01 01:10:00 2009-01-01 01:19:59 
2009-01-01 01:20:00 2009-01-01 01:29:59 
2009-01-01 01:30:00 2009-01-01 01:39:59 
2009-01-01 01:40:00 2009-01-01 01:49:59 
2009-01-01 01:50:00 2009-01-01 01:59:59 
.
I specified an interval_start and interval_end so you can aggregate the 
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;

이 게시물 의 맨 아래에있는 예제 데이터 시나리오에서 SQL Server에 대한 기능을 구축했습니다.

MSSQL의 경우 사용할 수 있습니다. 매우 빠 사용.

이를위한 테이블 값 함수 또는 종료 절차에 래핑하고 날짜 종료를 변수로 구문 분석 할 수 있습니다.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO

데이터가없는 날에 대해보고하고 싶다는 점에서 BIRT 보고서와 계속 문제가 있습니다. 날짜에 대한 항목이 없기 때문에 가장 쉬운 해결은 모든 날짜를 저장하는 간단한 테이블을 만들고이를 사용하여 범위를 가져 오거나 조인하여 해당 날짜에 대한 값을 0으로 만드는 것이 었습니다.

앞으로 5 년 동안 매달 실행되는 작업이 있습니다. 따라서 테이블이 생성됩니다.

create table all_dates (
    dt date primary key
);

의심 할 여지없이 다른 DBMS 로이 작업을 수행하는 마술처럼 까다로운 방법이 우리는 항상 가장 간단한 솔루션을 선택합니다. 테이블에 대한 저장소 요구 사항은 최소한이며 쿼리를 간단하고 이식 가능하게 만듭니다. 따라서 성능 관점에서 거의 항상 더 좋습니다.

다른 옵션 (이전에 사용했던)은 모든 날짜에 대한 항목이 테이블에 있는지 확인하는 것입니다. 우리는 주기적으로 테이블을 휩쓸고 존재하지 않는 날짜 및 / 또는 시간에 대한 항목을 추가했습니다. 이것은

이 경우 당신 실제로 는 유지하는 번거 로움 생각 all_dates채워 테이블, 저장 프로 시저는 그 날짜를 포함 하는 데이터 집합을 반환하는 길을 가야하는 것입니다 . 테이블에서 미리 계산 된 데이터를 가져 오는 것보다 호출 될 때마다 범위를 계산해야하기 때문에 거의 확실히 느립니다.

그러나 솔직히 말해서 심각한 데이터 저장 문제없이 1000 년 동안 테이블을 채울 수 있습니다. 16 바이트 (예 : 16 바이트) 날짜 365,000 개, 날짜를 복제하는 안전을 위해 20 % 오버 헤드를 추가 할 수 있습니다. 약 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 바이트]), 사물의 체계에서 아주 작은 표입니다.

다음 과 같이 MySQL의 사용자 변수를 사용할 수 있습니다 .

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num은 처음 사용할 때 추가하기 때문에 -1입니다. 또한 "HAVING date_sequence"는 각 행에 사용자 변수가 두 번 증가 할 때 사용할 수 없습니다.

이 답변 에서 아이디어를 빌려 0에서 9까지의 테이블을 설정하고이를 사용하여 날짜 목록을 생성 할 수 있습니다.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';

이렇게하면 최대 1000 개의 날짜를 생성 할 수 있습니다. 더 커야하는 경우 내부 쿼리에 다른 교차 조인을 추가 할 수 있습니다.

액세스 (또는 모든 SQL 언어)

  1. 이 개 필드가 하나의 테이블을 만들고, 우리는이 테이블에 전화 할게 tempRunDates:
    --fields fromDate및 toDate
    시작 날짜와 종료 날짜가-그런 다음 삽입 만 한 기록을.

  2. 다른 테이블 만들기 :- Time_Day_Ref
    날짜 목록을이 테이블로 가져옵니다.
    - 경우 필드 이름 제은 Greg_Dt그레고리 안 날짜의 경우
    -2009 년 1 월 1 일부터 2020 년 1 월 1 일까지 목록을 작성했습니다.

  3. 쿼리를 실행합니다.

    SELECT Time_Day_Ref.GREG_DT
FROM tempRunDates, Time_Day_Ref
WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;

쉬운!

일반적으로 다음과 같이 약간의 변형이있는이 목적을 위해 일반적으로 보관하는 보조 숫자 테이블을 사용합니다.

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

테이블 함수 반환 등의 변형을 보았습니다.

영구 날짜 목록을 작성합니다. 데이터웨어 하우스와 시간 목록에 있습니다.

SQL 서버에서 주어진 두 날짜 사이의 날짜를 찾는 방법은 http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html에 설명되어 있습니다 .

CREATE FUNCTION [dbo].[_DATES]
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS 
@DATES TABLE(
    DATE1 DATETIME
)
AS
BEGIN
    WHILE @startDate <= @endDate
    BEGIN 
        INSERT INTO @DATES (DATE1)
            SELECT @startDate   
    SELECT @startDate = DATEADD(d,1,@startDate) 
    END
RETURN
END

우리는 HRMS 시스템에서 사용했습니다.

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days

이 솔루션은 MySQL 5.0
테이블 만들기- mytable.
스키마는 중요하지 않습니다. 중요한 것은 그 안에있는 행의 수입니다.
따라서 10 개의 행, 값 (1 ~ 10)이있는 INT 유형의 열 하나만 사용할 수 있습니다.

SQL :

set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= '2011-07-31';

제한 : 위 쿼리에서 반환되는 최대 날짜 수는
(rows in mytable)*(rows in mytable) = 10*10 = 100.

이 범위는 SQL에서 양식 부분을 변경하여 늘릴 수 있습니다 :
MYTABLE X, MYTABLE Y, Z는 MYTABLE
따라서 범위는 수 10*10*10 =1000등입니다.

두 개의 매개 변수 a_begin 및 a_end를 사용하는 저장 프로 시저를 만듭니다. 그 안에 t라는 임시 테이블을 만들고, 변수 d를 선언하고, a_begin을 d에 할당하고, d를 t에 WHILE반복 INSERT하고 ADDDATE함수를 호출 하여 d 값을 증가시킵니다. 마지막으로 SELECT * FROM t.

다음과 외장을 사용합니다.

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

그런 다음 @HOLDERVariable 테이블에는 두 날짜 사이에 일 단위로 증가하는 모든 날짜가 저장되어 마음에 드는 콘텐츠에 참여할 준비가되어 있습니다.

나는 이것으로 꽤 오랫동안 싸웠습니다. 솔루션을 검색했을 때 구글에서 처음으로 히트 한 것이기 때문에 지금까지 얻은 위치를 게시하겠습니다.

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

[yourTable]데이터베이스의 테이블로 교체하십시오 . 트릭은 선택한 테이블의 행 수가 반환 될 날짜 수보다 크거나 같아야한다는 것입니다. 테이블 자리 표시 자 DUAL을 사용해 보았지만 하나의 행만 반환합니다.

select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

여기에서 먼저 현재 endDate에 하루를 추가하면 2011-02-28 00:00:00이됩니다. 그런 다음 1 초를 빼서 종료 날짜를 2011-02-27 23:59:59로 만듭니다. 이렇게하면 주어진 간격 사이의 모든 날짜를 얻을 수 있습니다.

산출 :
2011/02/25
2011/02/26
2011/02/27

DELIMITER $$  
CREATE PROCEDURE popula_calendario_controle()
   BEGIN
      DECLARE a INT Default 0;
      DECLARE first_day_of_year DATE;
      set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
      one_by_one: LOOP
         IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
            INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
         END IF;
         SET a=a+1;
         IF a=365 THEN
            LEAVE one_by_one;
         END IF;
      END LOOP one_by_one;
END $$

이 절차는 연초부터 지금까지의 모든 날짜를 삽입하고 "시작"과 "종료"의 날짜를 대체하기 만하면됩니다.

통계를 위해 두 날짜 사이의 모든 달 목록이 필요했습니다. 2 개의 날짜는 구독의 시작일과 종료일입니다. 따라서 목록에는 모든 월과 월별 구독 금액이 표시됩니다.

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
   -- Select the ultimate start and enddate from subscribers
   select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")), 
          @enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
   from subscription a;

   -- Tmp table with all months (dates), you can always format them with DATE_FORMAT) 
   DROP TABLE IF EXISTS tmp_months;
   create temporary table tmp_months (
      year_month date,
      PRIMARY KEY (year_month)
   );


   set @tempDate=@startdate;  #- interval 1 MONTH;

   -- Insert every month in tmp table
   WHILE @tempDate <= @enddate DO
     insert into tmp_months (year_month) values (@tempDate);
     set @tempDate = (date(@tempDate) + interval 1 MONTH);
   END WHILE;

   -- All months
   select year_month from tmp_months;

   -- If you want the amount of subscription per month else leave it out
   select mnd.year_month, sum(subscription.amount) as subscription_amount
   from tmp_months mnd
   LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
   GROUP BY mnd.year_month;

 END

MariaDB> = 10.3 및 MySQL> = 8.0에서 새로운 재귀 (공통 테이블 표현식) 기능을 사용하는 우아한 솔루션입니다.

WITH RECURSIVE t as (
    select '2019-01-01' as dt
  UNION
    SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
select * FROM t;

위는 '2019-01-01'과 '2019-04-30'사이의 날짜 테이블을 반환합니다.

나는 사용하고있다 Server version: 5.7.11-log MySQL Community Server (GPL)

이제 우리는 이것을 간단한 방법으로 해결할 것입니다.

"datetable" 이라는 이름의 테이블을 만들었습니다.

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid   | int(11) | NO   | PRI | NULL    |       |
| coldate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

이제 삽입 된 레코드를 볼 수 있습니다.

mysql> select * from datetable;
+-------+------------+
| colid | coldate    |
+-------+------------+
|   101 | 2015-01-01 |
|   102 | 2015-05-01 |
|   103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

여기에서는 해당 날짜가 아닌 두 날짜 내의 레코드를 가져 오는 쿼리입니다.

mysql> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate    |
+-------+------------+
|   102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

이것이 많은 사람들에게 도움이되기를 바랍니다.

참고 URL : https://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates

반응형

'IT' 카테고리의 다른 글

다른 div 내에서 첫 번째 div를 선택하는 데 사용할 수있는 CSS 선택 도구  (0) 2020.09.15
JavaFX는 WPF와 어떻게 다릅니 까?  (0) 2020.09.14
Ansible 플레이 북과 역할  (0) 2020.09.14
Google Cloud Datastore와 Firebase  (0) 2020.09.14
Java 프로세스를 중지하는 방법은 무엇입니까?  (0) 2020.09.14

'IT'의 다른글

  • 현재글두 날짜 사이의 날짜 목록 가져 오기

관련글

댓글

티스토리툴바