[postgresql] PostgreSQL에서 두 날짜 사이에 시계열 생성

Program / 글쓴이

주어진 두 날짜 사이에 일련의 날짜를 멋지게 생성하는 다음과 같은 쿼리가 있습니다.

select date '2004-03-07' + j - i as AllDate
from generate_series(0, extract(doy from date '2004-03-07')::int - 1) as i,
     generate_series(0, extract(doy from date '2004-08-16')::int - 1) as j

이 사이에 162 날짜를 생성 2004-03-07하고 2004-08-16이 내가 원하는 것을. 이 코드의 문제점은 두 날짜가 다른 연도의 날짜 인 경우 (예 : 내가 2007-02-01and 2008-04-01.

더 나은 해결책이 있습니까?

답변

int로 /에서 변환하지 않고 수행 할 수 있습니다 (대신 타임 스탬프로 /에서)

SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;

답변

일련의 날짜 를 생성하려면 이것이 최적의 방법입니다.

SELECT t.day::date
FROM   generate_series(timestamp '2004-03-07'
                     , timestamp '2004-08-16'
                     , interval  '1 day') AS t(day);

  • 추가 date_trunc()가 필요하지 않습니다. date( day::date)에 대한 캐스트 는 암시 적으로 수행합니다.

  • 그러나 날짜 리터럴을 date입력 매개 변수로 캐스팅하는 것도 의미가 없습니다 . Au contraire, timestamp최선의 선택 입니다. 성능상의 이점은 작지만 그것을 취하지 않을 이유가 없습니다. 그리고 당신은 불필요의 전환과 함께 규칙 DST를 (일광 절약 시간)이 포함되지 않습니다 date에 timestamp with time zone다시. 아래를 참조하십시오.

동등하고 덜 명시적인 짧은 구문 :

SELECT day::date
FROM   generate_series(timestamp '2004-03-07', '2004-08-16', '1 day') day;

또는 SELECT목록 의 세트 반환 기능을 사용하여 :

SELECT generate_series(timestamp '2004-03-07', '2004-08-16', '1 day')::date AS day;

AS키워드가되어 필요한 마지막 변형에서, 포스트 그레스는 열 별칭을 잘못 해석 할 day그렇지. 그리고 Postgres 10 이전의 변형은 적어도 동일한 목록 에 둘 이상의 집합 반환 기능 이있는 것은 권장하지 않습니다 SELECT.

(그 외에도 마지막 변형은 일반적으로 아주 작은 차이로 가장 빠릅니다.)

왜 timestamp [without time zone]?

의 오버로드 된 변형이 많이 generate_series()있습니다. 현재 (Postgres 11) :

SELECT oid::regprocedure   AS function_signature
     , prorettype::regtype AS return_type
FROM   pg_proc
where  proname = 'generate_series';

function_signature | 반환 _ 유형
: ------------------------------------------------- ------------------------------- | : --------------------------
generate_series (integer, integer, integer) | 정수
generate_series (integer, integer) | 정수
generate_series (bigint, bigint, bigint) | Bigint
generate_series (bigint, bigint) | Bigint
generate_series (숫자, 숫자, 숫자) | 숫자
generate_series (숫자, 숫자) | 숫자
generate_series (시간대없는 타임 스탬프, 시간대없는 타임 스탬프, 간격) | 시간대가없는 타임 스탬프
generate_series (시간대가있는 타임 스탬프, 시간대가있는 타임 스탬프, 간격) | 시간대가있는 타임 스탬프

( numeric변종은 포스트 그레스 9.5에 추가되었습니다.) 관련 사람 지난 2입니다 굵게 촬영 및 반환 timestamp/ timestamptz.

가져 가거나 반환하는 변형date 이 없습니다 . 을 반환하려면 명시 적 캐스트가 필요합니다 date. timestamp인수가 있는 호출은 함수 유형 확인 규칙으로 내려 가지 않고 입력에 대한 추가 캐스트없이 직접 최상의 변형으로 확인됩니다.

timestamp '2004-03-07'완벽하게 유효합니다, btw. 생략 된 시간 부분의 기본값 00:00은 ISO 형식입니다.

함수 유형 확인 덕분에 여전히 전달할 수 있습니다 date. 그러나 Postgres에서 더 많은 작업이 필요합니다. 가 암시 적 캐스트 에서 date에 timestamp뿐만 아니라 하나 date에은 timestamptz. 모호 할 것이지만 timestamptz있다 “선호” “날짜 / 시간 유형”중. 따라서 일치는 4d 단계에서 결정됩니다 . :

모든 후보를 살펴보고 선호하는 유형 (입력 데이터 유형의 유형 범주)을 허용하는 유형을 유형 변환이 필요한 대부분의 위치에 유지하십시오. 선호하는 유형을 허용하지 않는 경우 모든 후보를 유지하십시오. 후보가 하나만 남아 있으면 사용하십시오. 그렇지 않으면 다음 단계를 계속하십시오.

함수 유형 확인의 추가 작업 외에도 추가 캐스트가 timestamptz추가되어 비용이 더 많이 추가 될뿐만 아니라 DST 문제가 발생하여 드물게 예기치 않은 결과가 발생할 수 있습니다. (DST는 멍청한 개념입니다. btw는 이것을 충분히 강조 할 수 없습니다.) 관련 :

더 비싼 쿼리 계획을 보여주는 데모를 바이올린에 추가했습니다.

db <> 여기에 바이올린

관련 :

답변

날짜로 직접 시리즈를 생성 할 수 있습니다. int 또는 타임 스탬프를 사용할 필요가 없습니다.

select date::date
from generate_series(
  '2004-03-07'::date,
  '2004-08-16'::date,
  '1 day'::interval
) date;

답변

이것을 사용할 수도 있습니다.

select generate_series  ( '2012-12-31'::timestamp , '2018-10-31'::timestamp , '1 day'::interval) :: date

답변