1

Using PostgresQL 9.6, for a certain period of time, I have three input values:

  • "endDate": any date, being the end date
  • "months": a number of months between 0 and ca. 30
  • "days": a number of days between 0 and 29

The task is: Find the start date of that period. Requirement is: The result of age("end","start") (*) must always be the same as the input interval (month + days). In other words: Make the period storable with just start and end without explicitly saving the given interval, but making sure that the interval remains exactly the same.

My first simple attempt was

SELECT "endDate" - ("months" || ' mons ' || "days" ' days')::interval

However, that doesn't work for input

  • "endDate": 2017-06-29
  • "months": 4
  • "days": 19

The start date calculated by this approach will be 2017-02-09. And age('2017-06-29','2017-02-10') will return 4 mons 20 days which is one day too much.

The probable reason is that the minus operator most likely will first subtract the month, then if it lands on an "impossible date" like 2017-02-29, go to the previous "possible" date (here: 2017-02-28) and then subtracts the days, landing on 2017-02-09 - which is wrong here.

So, I came up with this idea:

WITH prep AS 
( SELECT ("months"||' mons')::interval AS moninterval, 
         ("days" || ' days')::interval AS dayinterval )

SELECT 
 CASE WHEN date_part('day',"endDate") > "days" 
   THEN (("endDate" - dayinterval) - moninterval)::date 
   ELSE ("endDate" - (  moninterval + dayinterval) )::date
END AS simstart
FROM prep

Basically, the idea is: If the number of days in the end date is bigger than the number of days in the interval, then first subtract the days, then the months. Otherwise, do as before.

That works for many cases. However, I still found an edge case where it doesn't:

  • "endDate": 2018-03-02
  • "months": 0
  • "days": 28

Regardless of the method used: The start date will always be calculated as 2018-02-02. And if you do SELECT age_forward('2018-03-02','2018-02-02') you will always end up with 1 mon - which is technically correct. However, it's wrong here, since the original input was 28 days.

(*) To be more precise: age_forward. See my answer to my own question here https://stackoverflow.com/a/51173709/2710714 However, I think it doesn't matter with the edge case problems described above.

cis
  • 1,259
  • 15
  • 48
  • 2
    Well, which day is March 31 minus a month and a day? Months are no precise interval we can calculate with, so the task doesn't make too much sense. If you want a startdate for which `age(enddate, startdate)` results in `' mons days'`, you can write a recursive query to go back day by day until you get the desired string. – Thorsten Kettner Sep 25 '18 at 09:45
  • 1
    Those dates in your second case are exactly 1 month apart, with happens to be 28 days. Something you noted yourself. Unless you are willing to change requirements, you won't get answer that could meet them. – Łukasz Kamiński Sep 25 '18 at 13:13

0 Answers0