Query last of several, discontinuous, ranges

user_3881501 0
Viewed 28

I have a postgresql table of weather station readings:

  CREATE TABLE "weather"
    (
    "id" SERIAL PRIMARY KEY,
    "dtstamp" TIMESTAMP NOT NULL UNIQUE,
    "temperature" REAL NOT NULL,
    ...
    )

For gardening, I'm interested in the dates of the first and last frosts.

I can get the last frost of the spring for 2019:

  SELECT dtstamp FROM weather WHERE temperature<0
    AND dtstamp BETWEEN '2019-01-01' AND '2019-07-01'
    ORDER BY dtstamp DESC LIMIT 1
  2019-04-13 00:55:16

And the first frost of autumn for 2019:

  SELECT dtstamp FROM weather WHERE temperature<0
    AND dtstamp BETWEEN '2019-07-01' AND '2019-12-31'
    ORDER BY dtstamp ASC LIMIT 1
  2019-10-28 03:59:20

Similarly for other years. How can I query this for a table over several years?

postgresql sql
1 Answers

You can extract the month for filtering and aggregate. For instance:

SELECT EXTRACT(year FROM dtstamp),
       MAX(dtstamp) FILTER (WHERE temperature < 0)
FROM weather
WHERE temperature < 0 AND
      EXTRACT(month FROM dtstamp) < 7
GROUP BY EXTRACT(year FROM dtstamp);

and:

SELECT EXTRACT(year FROM dtstamp),
       MIN(dtstamp) FILTER (WHERE temperature < 0)
FROM weather
WHERE temperature < 0 AND
      EXTRACT(month FROM dtstamp) >= 7
GROUP BY EXTRACT(year FROM dtstamp);

You can also put this in one query:

SELECT EXTRACT(year FROM dtstamp),
       MAX(dtstamp) FILTER (WHERE temperature < 0 AND EXTRACT(month FROM dtstamp) < 7) as latest_spring_frost,
       MIN(dtstamp) FILTER (WHERE temperature < 0 AND EXTRACT(month FROM dtstamp) >= 7) as earliest_autumn_frost
FROM weather
WHERE temperature < 0          
GROUP BY EXTRACT(year FROM dtstamp);
user_11440350
Related