Finding all rows that have a date in the current season in PostgreSQL

Question

I have a table of surveys in PostgreSQL. I want to find all the surveys that have been done since the start of the current season. I have written it this way:

WITH foo 
AS (

SELECT 
-- This case will find the date of the first day of this season
-- Year is selected based on the current date. This way we can use the same code next year.
-- ** finding the start of the season.
CASE 
    WHEN EXTRACT('month' FROM NOW()) >= 1 AND EXTRACT('month' FROM NOW()) < 4 
        THEN TO_TIMESTAMP( EXTRACT('year' FROM NOW())||'-01-01 01:00:00', 'YYYY-MM-DD HH:MI:SS')

    WHEN EXTRACT('month' FROM NOW()) >= 4 AND EXTRACT('month' FROM NOW()) < 7
        THEN TO_TIMESTAMP( EXTRACT('year' FROM NOW())||'-04-01 01:00:00', 'YYYY-MM-DD HH:MI:SS')

    WHEN EXTRACT('month' FROM NOW()) >= 7 AND EXTRACT('month' FROM NOW()) < 9 
        THEN TO_TIMESTAMP( EXTRACT('year' FROM NOW())||'-07-01 01:00:00', 'YYYY-MM-DD HH:MI:SS')

    WHEN EXTRACT('month' FROM NOW()) >= 10 AND EXTRACT('month' FROM NOW()) < 13 
        THEN TO_TIMESTAMP( EXTRACT('year' FROM NOW())||'-10-01 01:00:00', 'YYYY-MM-DD HH:MI:SS')

END start_season
)
SELECT *
FROM foo f,
my_table t
WHERE date_survey > start_season

This solution works. However, I am searching for a simpler solution if exists.

Erwin Brandstetter · Accepted Answer · 2021-03-19T16:19:20.840

Consider just date_trunc():

SELECT date_trunc('quarter', date_survey) AS start_timestamp
     , *
FROM   my_table t
WHERE  date_survey >= date_trunc('quarter', now());

Notably, both expressions produce the same result (except for the data type, possibly), while the WHERE clause is in place.

date_trunc('quarter', date_survey)
date_trunc('quarter', now())

But you might want to use the same query for the whole year or something. Then the added date_trunc('quarter', date_survey) is meaningfully different:

...
WHERE  date_survey >= date_trunc('year', now());

Or maybe add EXTRACT('quarter' FROM date_survey) AS start_season to add the quarter number.

Be aware of corner case pitfalls with type timestamp (or date) depending on the current time zone setting (as opposed to timestamptz).

See:

Finding all rows that have a date in the current season in PostgreSQL

1 Answers1