복붙노트

[SQL] 어떻게 연도를 무시 날짜 계산을해야합니까?

SQL

어떻게 연도를 무시 날짜 계산을해야합니까?

나는 14 일의 기념일이 날짜를 선택하려합니다. 어떻게 년을 제외한 날짜를 기준으로 선택할 수 있습니다? 나는 다음과 같은 일을 시도했습니다.

SELECT * FROM events
WHERE EXTRACT(month FROM "date") = 3
AND EXTRACT(day FROM "date") < EXTRACT(day FROM "date") + 14

이 문제는 그 달 랩입니다. 이 같은 일을하는 것을 선호하지만 올해 무시하는 방법을 모르겠어요.

SELECT * FROM events
WHERE (date > '2013-03-01' AND date < '2013-04-01')

어떻게 포스트 그레스의 날짜 수학 이런 종류의 작업을 수행 할 수 있습니까?

해결법

  1. ==============================

    1.당신이 설명과 자세한 내용은 상관하지 않는 경우, 아래의 "블랙 매직 버전"을 사용합니다.

    당신이 설명과 자세한 내용은 상관하지 않는 경우, 아래의 "블랙 매직 버전"을 사용합니다.

    그들이 인덱스를 사용할 수 없습니다와 일치하는 행을 찾기 위해 기본 테이블의 모든 단일 행에 대한 식을 계산해야 - 다른 답변에 제시된 모든 쿼리는 지금까지 스 SARGable하지 않은 상태로 작동합니다. 작은 테이블에 많은 문제가되지 않습니다. 사항 큰 테이블 (많은).

    다음의 간단한 테이블을 감안할 때 :

    CREATE TABLE event (
  event_id   serial PRIMARY KEY
, event_date date
);

    버전 1 이하 2. 형태의 간단한 인덱스를 사용할 수 있습니다 :

    CREATE INDEX event_event_date_idx ON event(event_date);

    그러나 다음과 같은 솔루션의 모든 인덱스없이 더 빨리합니다.

    SELECT *
FROM  (
   SELECT ((current_date + d) - interval '1 year' * y)::date AS event_date
   FROM       generate_series( 0,  14) d
   CROSS JOIN generate_series(13, 113) y
   ) x
JOIN  event USING (event_date);

    하위 쿼리 X 계산하는 CROSS에서 년의 주어진 범위에서 모든 가능한 날짜는 두 generate_series () 호출의 가입. 선택은 최종 간단한 가입을 이루어집니다.

    WITH val AS (
   SELECT extract(year FROM age(current_date + 14, min(event_date)))::int AS max_y
        , extract(year FROM age(current_date,      max(event_date)))::int AS min_y
   FROM   event
   )
SELECT e.*
FROM  (
   SELECT ((current_date + d.d) - interval '1 year' * y.y)::date AS event_date
   FROM   generate_series(0, 14) d
        ,(SELECT generate_series(min_y, max_y) AS y FROM val) y
   ) x
JOIN  event e USING (event_date);

    하여 발생 년 최소화 - 년의 범위는 자동으로 테이블에서 추론된다. 당신은 한 단계 더 나아가 차이가있는 경우 기존의 년의 목록을 증류 할 수있다.

    효과 날짜의 분포에 공동-따라 달라집니다. 많은 행이 몇 년 동안 각이 솔루션이 더 유용합니다. 몇 행 몇 년은 각각 덜 유용합니다.

    간단한 SQL 바이올린 함께 재생합니다.

    2016 업데이트하는 것은 H.O.T.을 차단하는 것 "생성 된 열을"제거하기 업데이트; 간단하고 빠르게 작동합니다. 함수 인라인을 허용하는 불변의 표현으로 MMDD을 계산하기 위해 2018 업데이트.

    패턴 'MMDD'의 정수를 계산하는 간단한 SQL 함수를 만듭니다 :

    CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int';

    I는 TO_CHAR (시간 'MMDD') 한 처음하지만 포스트 그레스 9.6 10 새로운 테스트에서 빠른 입증 상기 식 절환 :

    DB <> 바이올린 여기

    EXTRACT (날짜 XYZ)을 내부적으로 불변 기능 date_part (텍스트, 날짜)로 구현되어 있기 때문에 함수 인라인을 할 수 있습니다. 그리고 다음과 같은 필수적인 여러 열로 표현 인덱스에서의 사용을 허용하는 불변이어야한다 :

    CREATE INDEX event_mmdd_event_date_idx ON event(f_mmdd(event_date), event_date);

    여러 가지 이유로 다중 열 : ORDER BY 또는 주어진 년에서 선택에 도움이 될 수 있습니다. 여기에 읽기. 인덱스 거의 추가 비용없이. 그렇지 않으면 인해 데이터 정렬에 패딩 손실 될 것 4 바이트로 날짜를 맞 춥니 다. 여기에 읽기. 두 인덱스 열은 동일한 열 테이블을 참조하기 때문에, 더 H.O.T.에 대해서는 환급하지 업데이트. 여기에 읽기.

    두 쿼리 중 하나에 포크 올해의 차례를 포함합니다 :

    CREATE OR REPLACE FUNCTION f_anniversary(date = current_date, int = 14)
  RETURNS SETOF event AS
$func$
DECLARE
   d  int := f_mmdd($1);
   d1 int := f_mmdd($1 + $2 - 1);  -- fix off-by-1 from upper bound
BEGIN
   IF d1 > d THEN
      RETURN QUERY
      SELECT *
      FROM   event e
      WHERE  f_mmdd(e.event_date) BETWEEN d AND d1
      ORDER  BY f_mmdd(e.event_date), e.event_date;

   ELSE  -- wrap around end of year
      RETURN QUERY
      SELECT *
      FROM   event e
      WHERE  f_mmdd(e.event_date) >= d OR
             f_mmdd(e.event_date) <= d1
      ORDER  BY (f_mmdd(e.event_date) >= d) DESC, f_mmdd(e.event_date), event_date;
      -- chronological across turn of the year
   END IF;
END
$func$  LANGUAGE plpgsql;

    "오늘"을 시작 십사일 : 기본값을 사용하여 전화 :

    SELECT * FROM f_anniversary();

    '2014년 8월 23일'를 시작하는 7 일간 전화 :

    SELECT * FROM f_anniversary(date '2014-08-23', 7);

    비교 SQL 바이올린 분석 설명한다.

    기념일 또는 "생일"를 다룰 때, 당신은 윤년의 특별한 경우 "2월 29일"처리하는 방법을 정의 할 필요가있다.

    날짜의 범위를 테스트 할 때 2 월 (29)는 일반적으로 현재 연도가 윤년없는 경우에도 자동으로 포함됩니다. 일의 범위는이 날 커버 소급 때 1로 확장됩니다. 현재 연도가 윤년, 그리고 15 일 동안보고 싶은 경우 데이터가 아닌 윤년에있는 경우, 다른 한편으로, 당신은 윤년 14 일 결과를 얻는 끝낼 수 있습니다.

    말은 밥 2 월 29 일에 태어난 : 내 쿼리 1과 2는 윤년 2 월 (29) 등이 있습니다. 밥은 매 ~ 사년 생일이 있습니다. 내 쿼리 3. 범위 년 2 월 (29)이 포함되어 있습니다. 밥은 매년 생일이 있습니다.

    어떤 마법의 해결책은 없다. 당신은 당신이 모든 경우에 원하는 것을 정의해야합니다.

    나의 점을 입증하기 위해 나는 모든 제시된 솔루션과 광범위한 테스트를 실행했습니다. I 지정된 테이블에 질의 각각을 적응 ORDER BY없이 동일한 결과를 얻었다.

    좋은 소식 : 모두가 올바른지과 같은 결과를 얻을 수 - 그리고 @ wildplasser의 쿼리 구문 오류가 있었다 고든의 쿼리를 제외 해 주위에 래핑 할 때 실패 (쉽게 수정에).

    사는 사람 (13 세 이상)의 테이블과 유사하다 20 세기에서 임의의 날짜와 108,000 행을 삽입합니다.

    INSERT INTO  event (event_date)
SELECT '2000-1-1'::date - (random() * 36525)::int
FROM   generate_series (1, 108000);

    일부 죽은 튜플을 생성하고 테이블보다 "실제 생활"을 만들기 위해 ~ 8 %를 삭제합니다.

    DELETE FROM event WHERE random() < 0.08;
ANALYZE event;

    내 테스트 케이스는 99,289 행, 4012 안타를했다.

    WITH anniversaries as (
   SELECT event_id, event_date
         ,(event_date + (n || ' years')::interval)::date anniversary
   FROM   event, generate_series(13, 113) n
   )
SELECT event_id, event_date -- count(*)   --
FROM   anniversaries
WHERE  anniversary BETWEEN current_date AND current_date + interval '14' day;

    이외에도 사소한 최적화에서 주요 차이점은 CTE에 대한 필요성을 완전히 피할 올해 주년을 얻을 수 년 date_trunc ( '년', 나이 (CURRENT_DATE + 14 EVENT_DATE))의 정확한 금액을 추가하는 것입니다 :

    SELECT event_id, event_date
FROM   event
WHERE (event_date + date_trunc('year', age(current_date + 14, event_date)))::date
       BETWEEN current_date AND current_date + 14;

    SELECT *   -- count(*)   -- 
FROM   event
WHERE  extract(month FROM age(current_date + 14, event_date))  = 0
AND    extract(day   FROM age(current_date + 14, event_date)) <= 14;

    위의 "1. 간단한 버전"을 참조하십시오.

    위의 "2. 고급 버전"을 참조하십시오.

    위의 "3. 블랙 매직 버전"을 참조하십시오.

    SELECT * -- count(*)   
FROM  (SELECT *, to_char(event_date, 'MM-DD') AS mmdd FROM event) e
WHERE  to_date(to_char(now(), 'YYYY') || '-'
                 || (CASE WHEN mmdd = '02-29' THEN '02-28' ELSE mmdd END)
              ,'YYYY-MM-DD') BETWEEN date(now()) and date(now()) + 14;

    WITH upcoming as (
   SELECT event_id, event_date
         ,CASE 
            WHEN date_trunc('year', age(event_date)) = age(event_date)
                 THEN current_date
            ELSE cast(event_date + ((extract(year FROM age(event_date)) + 1)
                      * interval '1' year) AS date) 
          END AS next_event
   FROM event
   )
SELECT event_id, event_date
FROM   upcoming
WHERE  next_event - current_date  <= 14;

    CREATE OR REPLACE FUNCTION this_years_birthday(_dut date) RETURNS date AS
$func$
DECLARE
    ret date;
BEGIN
    ret :=
    date_trunc( 'year' , current_timestamp)
        + (date_trunc( 'day' , _dut)
         - date_trunc( 'year' , _dut));
    RETURN ret;
END
$func$ LANGUAGE plpgsql;

    모든 다른 사람과 같은를 반환 간체 :

    SELECT *
FROM   event e
WHERE  this_years_birthday( e.event_date::date )
        BETWEEN current_date
        AND     current_date + '2weeks'::interval;

    (이 이미 상당한 게시물의 범위를 벗어나는) 비효율적 인 세부 사항의 숫자에서 위 겪고있다. 재 작성된 버전은 훨씬 빠릅니다 :

    CREATE OR REPLACE FUNCTION this_years_birthday(_dut INOUT date) AS
$func$
SELECT (date_trunc('year', now()) + ($1 - date_trunc('year', $1)))::date
$func$ LANGUAGE sql;

SELECT *
FROM   event e
WHERE  this_years_birthday(e.event_date)
        BETWEEN current_date
        AND    (current_date + 14);

    나는 PostgreSQL을 9.1.7에 임시 테이블이 테스트를 실행했습니다. 결과는 5 최고의 분석 EXPLAIN에 모였다.

    Without index
C:  Total runtime: 76714.723 ms
C1: Total runtime:   307.987 ms  -- !
D:  Total runtime:   325.549 ms
E1: Total runtime:   253.671 ms  -- !
E2: Total runtime:   484.698 ms  -- min() & max() expensive without index
E3: Total runtime:   213.805 ms  -- !
G:  Total runtime:   984.788 ms
H:  Total runtime:   977.297 ms
W:  Total runtime:  2668.092 ms
W1: Total runtime:   596.849 ms  -- !

With index
E1: Total runtime:    37.939 ms  --!!
E2: Total runtime:    38.097 ms  --!!

With index on expression
E3: Total runtime:    11.837 ms  --!!

    그들은 비 스 SARGable 표현을 사용하기 때문에 다른 모든 쿼리 또는 인덱스없이 동일한을 수행합니다.

  2. ==============================

    2.나는 다음과 같은 테스트가 열 이름 anniv_date 가정, 모든 경우에 작동 생각 :

    나는 다음과 같은 테스트가 열 이름 anniv_date 가정, 모든 경우에 작동 생각 :

    select * from events
where extract(month from age(current_date+interval '14 days', anniv_date))=0
  and extract(day from age(current_date+interval '14 days', anniv_date)) <= 14

    년 (도 한 달) 교차 할 때 그것이 작동하는 방법의 예를 들어,하자가 기념일 날짜가 2009년 1월 4일 및 테스트 실행이 2012년 12월 29일입니다 된 날짜 말한다.

    우리는 2012년 12월 29일 및 2013년 1월 12일 (십사일) 사이의 날짜를 고려하는 것이 좋습니다

    나이 ( '2013년 1월 12일':: 날짜, '2009년 1월 4일':: 날짜) 사년 팔일입니다.

    이에서 추출물 (월 ...) (... 일) 0 추출물이 일치하도록보다 낮은 14 8입니다.

  3. ==============================

    3.이건 어때요?

    이건 어때요?

    select *
from events e
where to_char(e."date", 'MM-DD') between to_char(now(), 'MM-DD') and 
                                         to_char(date(now())+14, 'MM-DD')

    당신은 문자열로 비교를 할 수 있습니다.

    계정으로 년 끝을 위해, 우리는 날짜로 다시 변환 할 수 있습니다 :

    select *
from events e
where to_date(to_char(now(), 'YYYY')||'-'||to_char(e."date", 'MM-DD'), 'YYYY-MM-DD')
           between date(now()) and date(now())+14

    당신 이월 내가 제안 수 (29)에 대한 약간의 조정을해야합니까 :

    select *
from (select e.*,
             to_char(e."date", 'MM-DD') as MMDD
      from events
     ) e
where to_date(to_char(now(), 'YYYY')||'-'||(case when MMDD = '02-29' then '02-28' else MMDD), 'YYYY-MM-DD')
           between date(now()) and date(now())+14
  4. ==============================

    4.편의를 위해, 나는이 개 현재의 해에 (예상 또는 과거) birsthday를 산출 기능, 그리고 곧 생일을 만들었습니다.

    편의를 위해, 나는이 개 현재의 해에 (예상 또는 과거) birsthday를 산출 기능, 그리고 곧 생일을 만들었습니다.

    CREATE OR REPLACE FUNCTION this_years_birthday( _dut DATE) RETURNS DATE AS
$func$

DECLARE
        ret DATE;
BEGIN
        ret =
        date_trunc( 'year' , current_timestamp)
        + (date_trunc( 'day' , _dut)
          - date_trunc( 'year' , _dut)
          )
        ;
        RETURN ret;
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION next_birthday( _dut DATE) RETURNS DATE AS
$func$

DECLARE
        ret DATE;
BEGIN
        ret =
        date_trunc( 'year' , current_timestamp)
        + (date_trunc( 'day' , _dut)
          - date_trunc( 'year' , _dut)
          )
        ;
        IF (ret < date_trunc( 'day' , current_timestamp))
           THEN ret = ret + '1year'::interval; END IF;
        RETURN ret;
END;
$func$ LANGUAGE plpgsql;

      --
      -- call the function
      --
SELECT date_trunc( 'day' , t.topic_date) AS the_date
        , this_years_birthday( t.topic_date::date ) AS the_day
        , next_birthday( t.topic_date::date ) AS next_day
FROM topic t
WHERE this_years_birthday( t.topic_date::date )
        BETWEEN  current_date
        AND  current_date + '2weeks':: interval
        ;

    참고 : 만 가능 타임 스탬프를했기 때문에 캐스트가 필요하다.

  5. ==============================

    5.이뿐만 아니라 연말에 랩 어라운드를 처리해야합니다 :

    이뿐만 아니라 연말에 랩 어라운드를 처리해야합니다 :

    with upcoming as (
  select name, 
         event_date,
         case 
           when date_trunc('year', age(event_date)) = age(event_date) then current_date
           else cast(event_date + ((extract(year from age(event_date)) + 1) * interval '1' year) as date) 
         end as next_event
  from events
)
select name, 
       next_event, 
       next_event - current_date as days_until_next
from upcoming
order by next_event - current_date

    당신은 표현 next_event보다는 필터링 할 수 있습니다 - CURRENT_DATE은 "14 일"을 적용

    당신이 "곧"뿐만 아니라 "오늘"이 될 것입니다 이벤트를 고려한다면이 사건은 ...에만 필요합니다. 그렇지 않으면,이는 경우 문의 다른 부분을 줄일 수있다.

    내가 EVENT_DATE에 열 "날짜", "이름"합니다. 주로 예약어는 식별자로 사용하지 않아야하지만 날짜가 끔찍한 열 이름도 있기 때문이다. 그것은 당신이 저장 대해 아무것도 말해주지 않습니다.

  6. ==============================

    6.당신은 기념일의 가상 테이블을 생성하고 선택할 수 있습니다.

    당신은 기념일의 가상 테이블을 생성하고 선택할 수 있습니다.

    with anniversaries as (
  select event_date, 
         (event_date + (n || ' years')::interval)::date anniversary
  from events, generate_series(1,10) n
)
select event_date, anniversary
from anniversaries
where anniversary between current_date and current_date + interval '14' day
order by event_date, anniversary

    generate_series 호출 (1,10)의 각각에 대한 기념일 EVENT_DATE 10 년을 생성하는 효과를 갖는다. 내가 생산에 리터럴 값 (10)을 사용하지 않을 것입니다. 대신, 나도 하위 쿼리에서 사용할 년간의 오른쪽 숫자를 계산하는 것, 또는 나 100처럼 큰 문자를 사용하십시오.

    당신은 당신의 응용 프로그램에 맞도록 WHERE 절을 조정할 수 있습니다.

    가상 테이블 성능 문제가있는 경우 (당신이 "이벤트"의 행이 많은 경우), 기본 테이블은 동일한 구조를 갖는 공통 테이블 식을 대체합니다. 기본 테이블에 기념일을 저장하면 (특히 2월 29일 기념일, 말에 대한) 그 값이 명확하게하고, 같은 테이블에 쿼리는 인덱스를 사용할 수 있습니다. 단지 SELECT 문을 사용하여 50 만 행의 기념일 테이블을 조회하는 것은 위의 내 바탕 화면은 25ms 걸립니다.

  7. ==============================

    7.나는 그것을 할 수있는 방법을 발견했다.

    나는 그것을 할 수있는 방법을 발견했다.

    SELECT EXTRACT(DAYS FROM age('1999-04-10', '2003-05-12')), 
       EXTRACT(MONTHS FROM age('1999-04-10', '2003-05-12'));
 date_part | date_part 
-----------+-----------
        -2 |        -1

    그때 나는 단지 달이 0이고 일 미만 14 것을 확인할 수 있습니다.

    좀 더 우아한 해결책이있는 경우, 게시 마십시오. 나는 약간의 질문을 개방 떠날거야.

  8. ==============================

    8.나는 그것의 최신 기능을 봤이 발견, 그래서 내가 PostgreSQL을 함께 작동하지 않습니다 http://www.postgresql.org/docs/current/static/functions-datetime.html

    나는 그것의 최신 기능을 봤이 발견, 그래서 내가 PostgreSQL을 함께 작동하지 않습니다 http://www.postgresql.org/docs/current/static/functions-datetime.html

    내가 제대로 읽을 경우, 14 일의 이벤트를 찾는 것은 간단 같습니다 :

     where mydatefield >= current_date
 and mydatefield < current_date + integer '14'

    물론 내가 제대로 읽기되지 않을 수 있습니다.

    9. from https://stackoverflow.com/questions/15169410/how-do-you-do-date-math-that-ignores-the-year by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 외래 키 등을 사용하여 복합 기본 키  (0) 2020.05.03
[SQL] SQL의 가난한 저장 프로 시저 실행 계획 성능 - 매개 변수 스니핑  (0) 2020.05.03
[SQL] SQL 점 표기법  (0) 2020.05.03
[SQL] MySQL의 GROUP BY 동작  (0) 2020.05.03
[SQL] SQL Server에 대한 그룹화 된 문자열 집계 / LISTAGG  (0) 2020.05.03
,

티스토리툴바