How to specify psycopg2 parameter for an array for timestamps (datetimes)

Question

I'd like to run a PostgreSQL query in Python using psycopg2, which filters by a column of type timestamp without timezone. I have a long list of allowed values for the timestamp (rather than a range) and psycopg2 conveniently handles arrays, so I thought that this should work:

SELECT somestuff
FROM mytable
WHERE thetimestamp = ANY (%(times)s)

The times parameter is a list of datetime objects. I've also tried psycopg2.Timestamp(). They both translates to WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]) and unfortunately that fails with the following error:

operator does not exist: timestamp without time zone = text
LINE 3: WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I've confirmed this in pgAdmin as well, so it's not just psycopg2. What seems to be happening is that Postgres will not implicitly convert an array of strings into an array of timestamps. It will convert a single string fine and the array works fine if I explicitly add ::timestamp to each element in pgAdmin, but I don't know how to do that in psycopg2.

What's the best way of doing this, other than forgetting DB-API parameters and just building the long string of timestamps manually? Is there any way I can get it to cast to the correct type?

Answer 1

Try it like this:

SELECT somestuff
FROM mytable
WHERE thetimestamp = ANY (%(times)s::timestamp[])

Answer 2

If you use psycopg2 version 2.2.0 or newer, your original code should work, if you wrap the values in Timestamp() constructors, as you suggested.

The reason that it didn't work before was a bug in the psycopg2 implementation. The suggested workaround was to insert explicit casts into the SQL, as suggested in another answer.