Using date and time in queries

You can perform arithmetic operations with the datetime and timedelta in queries.

If the expression can be calculated in Python, Pony will pass the result of the calculation as a parameter into the query:

select(o for o in Order if o.date_created >= datetime.now() - timedelta(days=3))[:]

SELECT "o"."id", "o"."state", "o"."date_created", "o"."date_shipped",
       "o"."date_delivered", "o"."total_price", "o"."customer"
FROM "Order" "o"
WHERE "o"."date_created" >= ?

If the operation needs to be performed with the attribute, we cannot calculate it beforehand. That is why such expression will be translated into SQL:

select(o for o in Order if o.date_created + timedelta(days=3) >= datetime.now())[:]

SELECT "o"."id", "o"."state", "o"."date_created", "o"."date_shipped",
       "o"."date_delivered", "o"."total_price", "o"."customer"
FROM "Order" "o"
WHERE datetime("o"."date_created", '+3 days') >= ?

The SQL generated by Pony will vary depending on the database. Above is the example for SQLite. Here is the same query, translated into PostgreSQL:

SELECT "o"."id", "o"."state", "o"."date_created", "o"."date_shipped",
       "o"."date_delivered", "o"."total_price", "o"."customer"
FROM "order" "o"
WHERE ("o"."date_created" + INTERVAL '72:0:0' DAY TO SECOND) >= %(p1)s

If you need to use a SQL function, you can use the raw_sql() function in order to include this SQL fragment:

select(m for m in DBVoteMessage if m.date >= raw_sql("NOW() - '1 minute'::INTERVAL"))

With Pony you can use the datetime attributes, such as month, hour, etc. Depending on the database, it will be translated into different SQL, which extracts the value for this attribute. In this example we get the month attribute:

select(o for o in Order if o.date_created.month == 12)

Here is the result of the translation for SQLite:

SELECT "o"."id", "o"."state", "o"."date_created", "o"."date_shipped",
       "o"."date_delivered", "o"."total_price", "o"."customer"
FROM "Order" "o"
WHERE cast(substr("o"."date_created", 6, 2) as integer) = 12

And for PostgreSQL:

SELECT "o"."id", "o"."state", "o"."date_created", "o"."date_shipped",
       "o"."date_delivered", "o"."total_price", "o"."customer"
FROM "order" "o"
WHERE EXTRACT(MONTH FROM "o"."date_created") = 12