0

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.

2ToneTim
  • 3
  • 2
  • 1
    Because you are comparing two strings so you are getting lexicographical sort order, like a dictionary. Don't convert a date to a string, just compare the `timestamp` to a date literal. – JNevill Aug 23 '22 at 19:49
  • 1
    that is a string comparison, so the rules for dates does not work – nbk Aug 23 '22 at 19:49
  • Take a look at the answers here for a better way of doing this: [Getting results between two dates in PostgreSQL](https://stackoverflow.com/questions/10170544/getting-results-between-two-dates-in-postgresql) – JNevill Aug 23 '22 at 19:56
  • Because the date field being passed from another source is in the MM/DD/YYYY format I needed to figure out how to compare two dates in that format against the timestamp format. I think I've figured it out though. `aa.timestamp >= to_timestamp( '07/01/2021','MM/DD/YYYY') and aa.timestamp <= to_timestamp( '06/30/2022','MM/DD/YYYY')` – 2ToneTim Aug 23 '22 at 20:17

1 Answers1

1

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 <