Search code examples
postgresqldateto-char

Using to_char in where clause

Does anyone know why when I use the following I don't get any results:

where 
TO_CHAR(aa.timestamp :: date, 'MM/DD/YYYY') >= '07/01/2021'
AND 
TO_CHAR(aa.timestamp :: date, 'MM/DD/YYYY') <= '06/30/2022'

But when I change the month I can:

where 
TO_CHAR(aa.timestamp :: date, 'MM/DD/YYYY') >= '07/01/2021'
AND 
TO_CHAR(aa.timestamp :: date, 'MM/DD/YYYY') <= '07/30/2022'

I'm trying to get a count of activity from a financial year. The aa.timestamp is in timestamp without time zone format on a Postgres Db.

Solution

  • Don't convert your timestamp to a string, compare it to a date:

    where aa.timestamp >= date '2021-01-07'
  AND aa.timestamp < date '2022-07-01'

    Note that I changed the upper limit one day after the date you specified, but changed the operator from <= to <