0

I have a function which returns min_time and max_time which I would like to use in a SELECT query as constraints for a timestamp column. Below is how I can think to do it but I would prefer to not have to run the query twice.

SELECT timestamp, col FROM data
    WHERE timestamp BETWEEN 
        (SELECT min_time FROM get_time_limits()) 
        AND 
        (SELECT max_time FROM get_time_limits());

Is there a way to use multiple columns from a single nested SELECT query as constraints in another query?

Sample table:

CREATE TABLE test(timestamp TIMESTAMPTZ, col INTEGER);

INSERT INTO test (
    SELECT generate_series(
        NOW() - INTERVAL '10 days', 
        NOW(), 
        INTERVAL '1 day'), 
        generate_series(1, 11)
    );

Example min/max time:

'2023-03-23 00:00:00+00'::TIMESTAMPTZ
'2023-03-29 00:00:00+00'::TIMESTAMPTZ
4
  • Please consider following these suggestions. Do you want the same query to work for both SQL Server and Postgres? Why?
    – mustaccio
    Mar 31, 2023 at 23:10
  • I just need it to work for postgres. The function get_time_limits() returns min_time::TIMESTAMPTZ, max_time::TIMESTAMPTZ. The table, data, has columns timestamp::TIMESTAMPTZ, col INTEGER.
    – brunerm99
    Mar 31, 2023 at 23:17
  • I realized I mislabeled as sql-server. Tags updated to just postgres @mustaccio
    – brunerm99
    Mar 31, 2023 at 23:26
  • Is get_time_limits() a STABLE function? ({ IMMUTABLE | STABLE | VOLATILE })
    – Sahap Asci
    Apr 1, 2023 at 9:44

2 Answers 2

2

Make your function return a tstzrange then you can use something like this:

SELECT timestamp, col 
FROM data
WHERE timestamp <@ get_time_limits()

I haven't tried, but the following should work using the current function:

SELECT timestamp, col 
FROM data
WHERE timestamp <@ (select tstzrange(min_time, max_time, '[]') from get_time_limits()) 
1
  • This looks like it would work for the simple query I explained above but the actual query I am doing uses TimescaleDB's time_bucket_gapfill which requires a more specific start/end in the WHERE clause. I will accept the answer for others but will need to do some more digging for my use case. Thanks!
    – brunerm99
    Apr 1, 2023 at 13:40
0

Just do a join, using the function as a virtual table:

SELECT timestamp, col FROM data, get_time_limits()
    WHERE timestamp BETWEEN min_time AND max_time;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.