Postgre에서 두 날짜 사이의 시계열 생성SQL

Postgre에서 두 날짜 사이의 시계열 생성SQL

주어진 두 날짜 사이의 일련의 날짜를 적절하게 생성하는 다음과 같은 질문이 있습니다.

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-01그리고.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입력 매개 변수로 사용합니다.반대되는 것이 최선의 선택입니다.성능 면에서는 장점이 적지만, 굳이 가져가지 않을 이유는 없습니다.또한 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마지막 변형에는 키워드가 필요합니다. Postgres가 열 별칭을 잘못 해석할 수 있습니다.day그렇지않으면.그리고 나는 Postgres 10 이전의 변형 모델에 대해 조언하지 않을 것입니다 - 적어도 하나 이상의 세트 리턴 기능이 동일하지 않습니다.SELECT목록:

• SELECT 절에서 여러 세트 리턴 함수에 대해 예상되는 동작은 무엇입니까?

(단, 마지막 변형은 일반적으로 약간의 차이로 가장 빠릅니다.)

왜죠timestamp [without time zone]?

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

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

function_type | return_type:-------------------------------------------------------------------------------- | :--------------------------generate_series(숫자, 숫자, 숫자) | 정수generate_series(숫자, 숫자) | 정수generate_series(bigint, bigint, bigint) | bigintgenerate_series(bigint, bigint) | bigintgenerate_series(숫자, 숫자, 숫자) | 숫자generate_series(숫자, 숫자) | 숫자generate_series(시간대가 없는 타임스탬프, 시간대가 없는 타임스탬프, 시간대가 없는 타임스탬프) | 시간대가 없는 타임스탬프generate_series(표준 시간대와 함께 표시, 표준 시간대와 함께 표시) | 표준 시간대와 함께 표시되는 타임스탬프

(numeric변종은 Postgres 9.5와 함께 추가되었습니다.)관련된 것들이 대담하게 인수 및 반환하는 마지막 두 가지입니다.timestamp/timestamptz.

변형된 테이크아웃 또는 리턴은 없습니다.반환하려면 명시적 캐스트가 필요합니다.date의 timestamp인수는 함수 유형 확인 규칙으로 내려가지 않고 입력에 대한 추가 캐스트 없이 직접 최상의 변형으로 해결됩니다.

timestamp '2004-03-07'그건 완벽하게 유효해, btw.생략된 시간 부분의 기본값은 다음과 같습니다.00:00ISO 형식을 사용합니다.

기능 유형 해결 덕분에 여전히 통과할 수 있습니다.date하지만 그것은 Postgres의 더 많은 작업을 필요로 합니다.의 암묵적인 캐스팅이 있습니다.datetimestamp만 ▁from의 것뿐만 datetimestamptz것 애하겠만지매,.timestamptz는 "날짜/시간 유형" 중 "비활성"입니다.따라서 4d 단계에서 일치 여부가 결정됩니다.

모든 후보를 검토하고 선호하는 유형(입력 데이터 유형의 유형 범주)을 허용하는 유형을 유형 변환이 필요한 가장 많은 위치에 유지합니다.원하는 유형을 허용하지 않는 경우 모든 후보를 유지합니다.후보가 하나만 남아 있으면 해당 후보를 사용하고, 그렇지 않으면 다음 단계로 계속 진행합니다.

인 작업 인 캐스트를 합니다.timestamptz이는 비용을 추가할 뿐만 아니라 드물게 예상치 못한 결과를 초래하는 DST 문제를 야기할 수 있습니다.(DST는 바보 같은 개념이지만, 아무리 강조해도 지나치지 않습니다.)관련:

• PostgreSQL에서 날짜 시리즈를 생성하려면 어떻게 해야 합니까?
• PostgreSQL에서 시계열을 생성하려면 어떻게 해야 합니까?

더 비싼 쿼리 계획을 보여주는 데모를 피들에 추가했습니다.

db<>여기로 이동

관련:

• Postgres에서 기능 오버로딩을 비활성화할 수 있는 방법이 있습니까?
• 일련의 날짜 생성 - 날짜 유형을 입력으로 사용
• Postgres 데이터 유형 캐스트

날짜와 함께 영상 시리즈를 직접 생성할 수 있습니다.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 

언급URL : https://stackoverflow.com/questions/14113469/generating-time-series-between-two-dates-in-postgresql