Selecting columns based on time ranges

Question

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?

Answer 1

Don't use BETWEEN for timestamps.
I also inverted the logic in the CASE expression to simplify, and to not exclude values for timestamp IS NULL (which may or may not be allowed).

SELECT CASE WHEN timestamp >= '2017-06-12T17:01' AND timestamp < '2018-06-12T21:58'
              OR timestamp >= '2020-06-12T17:01' AND timestamp < '2021-02-15T21:58' THEN null ELSE a END AS a
     , CASE WHEN timestamp >= '2018-07-14'       AND timestamp < '2019-01-02'
              OR timestamp >= '2020-06-12T17:01' AND timestamp < '2021-02-15T21:58' THEN null ELSE b END AS b
     , c
FROM   t1
WHERE  timestamp > '2014-01-01';

We don't need parentheses. Operator precedence works in our favor in either query.