[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-bugs
Subject: Re: BUG #15551: Date/Time comparison not correct when the comparison is inside join clause and invol
From: Andrew Gierth <andrew () tao11 ! riddles ! org ! uk>
Date: 2018-12-14 11:32:31
Message-ID: 87o99oz6w8.fsf () news-spur ! riddles ! org ! uk
[Download RAW message or body]
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:
PG> Note: the fourth quarter (*-12-31) of all years are missing.
So the problem here is that you're trying to identify quarters by the
_last_ date, not by the _first_ date.
If you add 3 months to Sept 30th, you get Dec 30th, NOT Dec 31st.
It so happens that adding 3 months to Mar 31st gives Jun 30th, and
adding 3 months to Jun 30th gives Sep 30th, and adding 3 months to
Dec 31st gives Mar 31st, so it's only the one case that fails here.
But if you identify the quarter by its _first_ date, you have no
problem.
date_trunc('quarter', somedate::timestamp) can help with this, but
remember to cast the date to timestamp (without time zone) in the call,
otherwise you'll get incorrect results due to timezone issues.
PG> The only difference among these query lies in the join clause
PG> involving date / time comparison having date / time operators being
PG> used:
PG> 1st: ct1."time" = ct2."time" + interval '3 months'
PG> 2nd: ct2."time" = ct1."time" - interval '3 months'
PG> 3rd: ct1."time" = ct2."time" + interval '3 months' or ct2."time" =
PG> ct1."time" - interval '3 months'
PG> It seems to me all the 3 conditions are logically same and should
PG> have the same result.
Well, in the presence of months of variable lengths, they clearly are
not equivalent and cannot be. So no bug here.
--
Andrew (irc:RhodiumToad)
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic