Группы, по сезону (Северном и Южном полушарии)

У меня есть точки данных, распределенные по всему земному шару. Каждая точка данных имеет метку времени.

Я хочу сгруппировать данные по сезонам, как показано на этой картинке (источник: https://en.wikipedia.org/wiki/Season#/media/File:Seasons1.svg).

Введите описание изображения здесь

Вот что я пытался сделать до сих пор (зима 2011/2012 в северном полушарии): 

SELECT * FROM my_table 
    WHERE my_date BETWEEN '2011-12-21' AND '2012-03-21'
    AND ST_Y(ST_Centroid(geom)) > 0;

Как я могу делать это в течение всех возможных лет? Я пытался ...Или дата между ними... но это очень медленно.

Как можно ли выбрать и зиму в Северном, и в Южном полушарии?

Обновить

Для всех зимних данных по Северному полушарию я использую такой запрос: 

CREATE TABLE season_winter_north AS WITH first_step AS (
SELECT
    id,
    extract(month from my_date)::int AS month,
    extract(day from my_date)::int AS day,
    ST_Centroid(geom) AS geom
FROM mytable 
    WHERE ST_Y(ST_Centroid(geom)) > 0)

(SELECT * FROM first_step
    WHERE month = 12
        AND day >= 21)

UNION ALL

(SELECT * FROM first_step
    WHERE month = ANY('{1,2}'))

UNION ALL

(SELECT * FROM first_step
    WHERE month = 3
        AND day<21)
Есть ли более элегантное или эффективное решение?
3 2
postgresql date postgis between

3 ответа:

with t(my_date) as (
    values ('2012-07-01'::date), ('2013-03-30'), ('2013-10-12'), ('2013-01-10')
)
select 
    case 
        when my_date between 
            make_date(extract(year from my_date)::int, 3, 21)
            and
            make_date(extract(year from my_date)::int, 6, 20)
        then 'spring'
        when my_date between 
            make_date(extract(year from my_date)::int, 6, 21)
            and
            make_date(extract(year from my_date)::int, 9, 22)
        then 'summer'
        when my_date between 
            make_date(extract(year from my_date)::int, 9, 23)
            and
            make_date(extract(year from my_date)::int, 12, 20)
        then 'fall'
        else 'winter'
    end as season,
    my_date
from t
;
season |  my_date   
--------+------------
summer | 2012-07-01
spring | 2013-03-30
fall   | 2013-10-12
winter | 2013-01-10
0

Более кратко и, вероятно, с лучшей производительностью: 

with t(my_date) as (
values ('2012-07-01'::date), ('2013-03-30'), ('2013-10-12'), ('2013-01-10') )
    select 
    case 
        when to_char(my_date,'MMDD') between '0321' and '0620' then 'spring'
        when to_char(my_date,'MMDD') between '0621' and '0922' then 'summer'
        when to_char(my_date,'MMDD') between '0923' and '1220' then 'fall'
        else                                                        'winter'
    end as season,
    my_date
    from t;
1

Я хочу опубликовать этот дополнительный ответ на мой вопрос. В этом запросе нет необходимости использовать функцию make_date (доступна только в postgresql 9.4.).

С помощью кода из этого ответа Вы можете создать функцию make_date() для других версий PostgreSQL.

Я также создал эту функцию и обнаружил значительную разницу в скорости. Но я не знаю, если функция make_date () в 9.4. более быстрый.

select 
case 
    when my_date between 
        (extract(year from my_date)::int||'-03-21')::date
        and
        (extract(year from my_date)::int||'-06-20')::date
    then 'spring'
    when my_date) between 
        (extract(year from my_date)::int||'-06-21')::date
        and
        (extract(year from my_date)::int||'-09-22')::date
    then 'summer'
    when my_date) between 
        (extract(year from my_date)::int||'-09-23')::date
        and
        (extract(year from my_date)::int||'-12-20')::date
    then 'fall'
    else 'winter'
end as season,
my_date
from my_table;

Запрос без функция make_date () работает примерно в 3-4 раза быстрее, чем функция make_date.

0