2

I have found many Questions and Answers about a SELECT excluding rows with a value "NOT IN" a sub-query (such as this). But how to exclude a list of values rather than a sub-query?

I want to search for rows whose timestamp is within a range but exclude some specific date-times. In English, that would be:

Select all the ORDER rows recorded between noon and 2 PM today except for the ones of these times: Today 12:34, Today 12:55, and Today 13:05.

SQL might be something like:

SELECT * FROM order_ WHERE recorded_ >= ? AND recorded_ < ? AND recorded_ NOT IN ( list of date-times… ) ;

So two parts to this Question:

  • How to write the SQL to exclude rows having any of a list of values?
  • How to set an arbitrary number of arguments to a PreparedStatement in JDBC?
    (the arbitrary number being the count of the list of values to be excluded)
Improve this question
4
  • your SQL will work to exclude values as described. stackoverflow.com/questions/178479/… answers the NOT IN list. Commented Jul 9, 2015 at 1:43
  • You want recorded_ <> ALL( ? ), with a param of type timestamp[]. Not sure how to feed this in via JDBC, though. Commented Jul 9, 2015 at 2:05
  • @NickBarnes: NOT IN is effectively the same as <> ALL(): stackoverflow.com/a/31192557/939860. The problem with both: they fail if NULL is involved on either side of the expression. The expression evaluates to NULL; but only TRUE passes a WHERE condition: stackoverflow.com/a/19528722/939860 Commented Jul 9, 2015 at 3:36
  • As explained in the comments, NOT IN (...) is equivalent to <> ALL (ARRAY[...]). You can create an array in JDBC with createArrayOf, then pass that as a parameter. Commented Jul 9, 2015 at 6:33

2 Answers 2

Reset to default
3

Pass array

A fast and NULL-safe alternative would be a LEFT JOIN to an unnested array:

SELECT o.* FROM order_ o LEFT JOIN unnest(?::timestamp[]) x(recorded_) USING (recorded_) WHERE o.recorded_ >= ? AND o.recorded_ < ? AND x.recorded_ IS NULL;

This way you can prepare a single statement and pass any number of timestamps as array.

The explicit cast ::timestamp[] is only necessary if you cannot type your parameters (like you can in prepared statements). The array is passed as single text (or timestamp[]) literal:

'{2015-07-09 12:34, 2015-07-09 12:55, 2015-07-09 13:05}', ...

Or put CURRENT_DATE into the query and pass times to add like outlined by @drake . More about adding a time / interval to a date:

Pass individual values

You could also use a VALUES expression - or any other method to create an ad-hoc table of values.

SELECT o.* FROM order_ o LEFT JOIN (VALUES (?::timestamp), (?), (?) ) x(recorded_) USING (recorded_) WHERE o.recorded_ >= ? AND o.recorded_ < ? AND x.recorded_ IS NULL;

And pass:

'2015-07-09 12:34', '2015-07-09 12:55', '2015-07-09 13:05', ...

This way you can only pass a predetermined number of timestamps.

Asides

For up to 100 parameters (or your setting of max_function_args), you could use a server-side function with a VARIADIC parameter:

I know that you are aware of timestamp characteristics, but for the general public: equality matches can be tricky for timestamps, since those can have up to 6 fractional digits for seconds and you need to match exactly.

Related

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
SELECT * FROM order_ WHERE recorded_ BETWEEN (CURRENT_DATE + time '12:00' AND CURRENT_DATE + time '14:00') AND recorded_ NOT IN (CURRENT_DATE + time '12:34', CURRENT_DATE + time '12:55', CURRENT_DATE + time '13:05') ;
Improve this answer

1 Comment

I'm using Java and JDBC. Can I make the list in parens simply a list of java.sql.Timestamp instances? Like this: NOT IN ( ? , ? , ? ) with `preparedStatement.setTimestamp( 3 , timestampObject1 ); ", repeating for 4, 5 and so on?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.