postgres date extract searching doesn't work how you would think

[Jaymon]

Let's say you created a query like this:

stop_dt = datetime.datetime.utcnow()

query.lte_stop(
    year=stop_dt.year,
    month=stop_dt.month,
    day=stop_dt.day
)

and you expect it to give you anything that is less than or equal to right now (in this case, right now is March 22, 2019), but what it will actually do is create a query like this:

SELECT
  *
FROM
  "table_name"
WHERE
  EXTRACT(YEAR FROM "stop") <= 2019 
  AND EXTRACT(DAY FROM "stop") <= 22 
  AND EXTRACT(MONTH FROM "stop") <= 3

Which is not what you want, because this will basically return results that have the year less than 2019, and also the day less than 22, and finally the month less than 3, so Februrary 20, 2018 would be returned but February 25, 2018 results wouldn't (because day is greater than 22).

This is just something to be aware of, the solution is just to use the actual datestamps:

stop_dt = datetime.datetime.utcnow()

query.lte_stop(stop_dt)

[Jaymon]

https://www.postgresql.org/docs/8.3/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

[Jaymon]

This just bit me again.

I could modify certain methods (eg, lte_field, gte_field) to change how they handle the date, so doing something like:

stop_dt = datetime.datetime.utcnow()

query.lte_stop(
    year=stop_dt.year,
    month=stop_dt.month,
)

Would do something like this internally:

start_day = 1
weekday, day = calendar.monthrange(year, month)
dt = datetime.date(year, month, day)

# pass dt to gte, gt, lt, lte

I'd need to account for all the fields you can pass (eg, day, month, year, week) but I could probably convert them to a date or datetime object that would work