postgresql - PostgreSQL按季节分组(北半球和南半球)

我有分布在全球的数据点。每个数据点都有时间戳。
我想按季节对数据进行分组，如图所示（来源：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)

有更优雅或更有效的解决方案吗？

最佳答案

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

关于postgresql - PostgreSQL按季节分组(北半球和南半球)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32393295/