Sample Header Ad - 728x90

Selecting columns based on time ranges

0 votes
1 answer
160 views
I have a table which looks like this:
Table t1

timestamp (pkey)     | A  | B | C
----------------------------------
2024-01-01 12:00:00  | 1  | 2 | 2
2024-01-01 12:00:01  | 1  | 2 | 2
2024-01-01 12:00:03  | 15 | 4 | 2
Table creation SQL:
CREATE TABLE IF NOT EXISTS t1 ("id" serial NOT NULL, "timestamp" timestamp PRIMARY KEY, "A" real, "B" real, "C" real)
Some tables might have 100+ columns. I'd like to select several columns having omitting time ranges on per column basis. The way I do it today is like the following:
SELECT 
(CASE WHEN timestamp NOT BETWEEN '2017-06-12T17:01' AND '2018-06-12T21:57'
                    AND timestamp NOT BETWEEN '2020-06-12T17:01' AND '2021-02-15T21:57' THEN A ELSE NULL END) "A",

(CASE WHEN timestamp NOT BETWEEN '2018-07-14' AND '2019-01-01'
                    AND timestamp NOT BETWEEN '2020-06-12T17:01' AND '2021-02-15T21:57' THEN B ELSE NULL END) "B",

C
FROM t1
WHERE timestamp > '2014-01-01'
timestamp is an index obviously. Is there a better / more effective way to do it?
Asked by Miro (113 rep)
Mar 25, 2024, 07:22 PM
Last activity: Jul 14, 2025, 10:04 AM