July 10th, 2003, 01:17 AM
Divison of intervals
I encountered problem when was trying to divide one date interval into another one. My task was: I have start_date, end_date and period defined by 3 numbers: in days, in month, in years (period_days, period_month, period_years).
I need to calculate how many complete periods passed between start_date and end_date. So formula will be something like:
(start_date-end_date)/interval 'period_days days period_month month period_years years'
But probllem is that division of itnervals is now allowed in PostgreSQL. So if anyone has ideas how to solve this puzzle, I would be very greateful!
July 10th, 2003, 02:07 AM
I tried to do something similar once, but got frustrated very quickly and decided that it's better to do the calculations in "programming language". So I did it with PHP.
Not much of helps, but anyway...
July 11th, 2003, 12:31 AM
As with many problems in SQL, this is the sort of problem that needs to be broken into pieces.
1. First: of course you can't divide an interval, because it is not a scalar type; it is a composite.
2. So, you need to decide what values you want out of that interval to divide. Are your intervals measured in days? Weeks? Hours? What is the level of granularity you need?
3. After deciding which part of the interval you need, then you need to know how to get that value out of the interval, and how to cast it to an integer so it can be divided.
Here is a quick example:
Notice what has happened here: first, we select an interval, which is NOW() minus '2002-09-11 03:29:15'. Then, we apply the date_part function to extract only days from that interval (we also could get hours or minutes if we want, but that would have to be added as two more calls). So, then we cast that value to an integer with '::int4', and then divide the whole amount (303) by 13, resulting in 23.
) / 13);
and the answer you should get is:
Unfortunately, there are some things to remember with date_part and intervals: it is not cumulative. For example, "date_part('seconds'" in the above example would not yield hundreds of thousands of seconds, but only a number between 0-60, based on the seconds of that minute. So, if you want more precision, you have to extract days, hours, minutes, and then seconds. Fortunately, any calculated intervals larger than a day tend to be measured internally in days, so you don't need to start higher than that, unless you are extracting the times from an interval specified as interval '4 years 5 months 4 days 24 minutes'. In this case, you would need to extract each of the fields and do the math needed to manipulate them into the number of days in question:
SELECT date_part('days', interval '3 years 4 months 2 days');
July 11th, 2003, 04:17 AM
yes, I understood your explanation, but my problem is mroe compliated: I need to divide not into defined integer number (in your example, you use number 13), but into interval of type interval ' 1 year 1 month 5 days' , for instance.
And I cannot calculate how many days thid interval contains.
So when I calculate difference in days between 2 dates, I need to divide this integer values into integer amount of days contained in my period interval, to get desired amount of complete periods passed between 2 dates.
July 11th, 2003, 10:21 AM
But still, it's just work; You have to turn that second interval into an integer, using a combination of the functions available to you.
Now, if you spend a bit of time thinking about it, you can come up with a bulletproof stored procedure which you can use anywhere to divide one integer by another, and for this, you only need 2 steps:
1. If 'days' are the useful value here, then you just have to come up with a method to accurately extract the full number of days from any interval, even if that interval is expressed as '3 years 4 months' etc... So, make a procedure to do this part, and then you can reuse it as an interval-manipulation function anywhere. This function, at most will take repeated use of the date_part() function to check for every type of value from millenium, century, year, on down to microseconds, and then do the requisite math to convert all those into days.
2. Make an interval-division function simply by calling the day-extraction function above on the intervals in question, and then dividing the resulting number of days.