tag:blogger.com,1999:blog-1378961241059327992.post1760547332458391061..comments2023-04-05T09:35:55.180+02:00Comments on KiBeHa: Temporal validity and open/closed intervalsKim Berg Hansenhttp://www.blogger.com/profile/06491635470794828550noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-1378961241059327992.post-46730629967880186232017-01-23T17:35:18.332+01:002017-01-23T17:35:18.332+01:00Hi Kim & Stew,
Thanks a lot for your thorough...Hi Kim & Stew,<br /><br />Thanks a lot for your thorough analysis :)<br /><br />The problem arises from the usual speaking (and Oracle !) semantics of the word BETWEEN,<br />which means an interval closed at both edges.<br /><br />Thus, the combination of BETWEEN with the temporal validity semi-closed interval logic,<br />like in the VERSIONS PERIOD FOR ... BETWEEN query is the cause of the "missing edge"<br />effect that we see.<br /><br />Temporal validity seems "more naturally suited" for the one-point-in-time<br />AS OF PERIOD FOR queries than for the BETWEEN closed interval logic.<br /><br /><br />It is always easier "to philosophize" after, than while playing a quiz in a competition :)<br /><br />And, just like Kim, I myself should thank the "$deity" once again<br />for having given us the treasure of Kim's quizzes and web posts :) :) :)<br /><br />Cheers & Best Regards,<br />Iudith<br /><br />iudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-89989617923909031652017-01-23T11:20:38.822+01:002017-01-23T11:20:38.822+01:00Hi, Stew
I figured it was just a matter of time b...Hi, Stew<br /><br />I figured it was just a matter of time before you would comment - you were just faster than I thought ;-)<br /><br />Anyway - no, my little "1 second overlap" does not report change in both months, only one. I do my November change report as shown in the blog post with:<br /><br /> from prices versions period for valid_price<br /> between to_date('2016-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')<br /> and to_date('2016-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')<br /><br />That shows 2 Beachball price changes - one in the month, one at the month switch.<br />Then I do my December change report with:<br /><br /> from prices versions period for valid_price<br /> between to_date('2016-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')<br /> and to_date('2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')<br /><br />And it does not show any beachball price changes.<br /><br />You're right, though, that from a user perspective, it would probably be better to have my one-second overlap in the other direction, so it shows in December change report instead.<br /><br /> from prices versions period for valid_price<br /> between date '2016-12-01' - interval '1' second<br /> and date '2016-12-01' + interval '1' month - interval '1' second<br /><br />That'll also do the trick and get me same results as your query.<br /><br />For this use case, I think your query is "neater". Having to do this "trick" of a one-second overlap is kind of unfortunate for temporal validity, but I don't see a way that it could be different.<br /><br />Main point is to beware of this, as temporal validity syntax makes it easy to "fall into the trap" that I did ;-)<br /><br />Thanks for the comment<br />/Kim<br />Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-17983322553038776752017-01-23T10:48:53.949+01:002017-01-23T10:48:53.949+01:00Hi Kim,
Before, there was a change that you didn&...Hi Kim,<br /><br />Before, there was a change that you didn't report in either month. Now that change is reported in both months.<br /><br />Personally, as a user I would want the change reported once, in this case in December when the new price became effective.<br /><br />select * from (<br /> select ITEM, VALID_UP_TO price_change_date, PRICE old_price,<br /> lead(price) over(partition by item order by valid_from nulls first) new_price<br /> from prices<br /> where (valid_up_to is null or valid_up_to >= date '2016-12-01')<br /> and (valid_from is null or valid_from < add_months(date '2016-12-01',1))<br />)<br />where new_price is not null<br />order by item, price_change_date;<br /><br />Of course, this doesn't use temporal validity syntax anymore, which is unfortunate...<br /><br />Best regards, StewStew Ashtonhttps://www.blogger.com/profile/10004507258457241890noreply@blogger.com