Postgres Interval not working in where subquery

Question

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.

Why are you casting the value in the second query to a date but not in the first? — user330315
– user330315, Commented Aug 31, 2020 at 13:16

Gordon Linoff · Accepted Answer · 2020-08-31 14:11:24Z

1

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.

answered Aug 31, 2020 at 14:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2020-08-31 13:16:57Z

0

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'

answered Aug 31, 2020 at 13:16

user330315

2 Comments

Amdbi Over a year ago

Weird enough with the approach you suggested I get the same null result (and no, it shouldn't be null, I do have data going back 6 months)

user330315 Over a year ago

It can only be null if the table contains no rows at all.

Collectives™ on Stack Overflow

Postgres Interval not working in where subquery

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related