CODEWITHSUNDEEP
sqlpostgresql
Amdbi
Amdbi

Reputation: 11

Postgres Interval not working in where subquery

I have the following code:

Select * from table
where to_date <= ( select max(to_date)
                  FROM table)
  and to_date >= (select (max(to_date)::date - interval '6 months')::date as to_date
                  FROM table)

Basically, I am trying to look at all the results between the max date and then 6 months in the past, and I tried doing that by making 2 sub queries.

I seem to get null, but oddly enough, if add the regular date that

(select (max(to_date)::date - interval '6 months')::date

is giving and paste it as >='yyyy-mm-dd', the query seems to be working fine. It is weird as both sub-queries are actually spitting out date format results and have no idea why its giving this.

Upvotes: 0

Views: 495

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You don't need both comparison:

select *
from table
where to_date >= (select (max(to_date)::date - interval '6 months')::date as to_date
                  from table
                 ) ;

This is assuming that the table reference is the same in both the inner and outer query.

Upvotes: 1

user330315
user330315

Reputation:

I can't really think of a reason why this wouldn't work, but you can rewrite the query to only run a single sub-query, which is also more efficient:

select t.*
from the_table t
  cross join (
    select max(the_date) as max_date
    from the_table
  ) mt
where t.to_date <= mt.max_date 
  and t.to_date >= mt.max_date - interval '6 months'

Upvotes: 0

Related Questions