Add an integer column as seconds to a datetime column in filter

[Guido Winkelmann]

Hi,

How can I add one column containing integer values (NULL allowed), treating
them as seconds, to another column from the same table containing datetime
values and compare the result to the current time?

I have a table with a datetime column "finished_on" and in int column "wait".
Once wait seconds have passed since finished_on, I need to do something with
that row. In postgres, the query to find these rows would look something like
this:

SELECT * FROM test WHERE finished_on + INTERVAL '1 sec' * wait < NOW();

How can I make a query like that in sqlalchemy?

I have tried googling the problem, but the only solutions I found where those
where you already have the interval value available in the calling python
code. This doesn't help me here, since the interval can be different for each
row.

Regards,

Guido W.

[Michael Bayer]

you should be able to cast as interval:


from sqlalchemy.dialects.postgresql import INTERVAL
from sqlalchemy import cast

select([test]).where(test.c.finished_on + cast(‘1 sec’, INTERVAL) * test.c.wait < func.NOW())

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Guido Winkelmann]

Does this only work on PostgreSQL? I need this to work on PostgreSQL, MySQL and SQLite.

[Michael Bayer]

all three have different ways of doing date arithmetic so you’d need to build out an expression object such as “add_seconds()” that handles it individually on each backend (look in the docs for the custom compilation extension). SQLA doesn’t have a date arithmetic system right now (it would be a huge deal to implement).