Search Postgresql Archives

Re: finding if a period is multiples of a given interval

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 01/28/2013 05:24 AM, c k wrote:
Hi,
I have two variables in pl/pgsql function.
p_fromdate and p_todate

I have another variable which represents intervals like day, month, quarter etc. p_interval as smallint, to hold values like 1,2,3, which are substituted for intervals as '1 day', '1 month - 1 day', '3 months - 1 day' respectively. Now, I have to find if the period of given two dates (p_todate - p_fromdate) is multiples of the given interval or not?

e.g. p_fromdate = '01/04/2010';
p_todate = '31/03/2013';

p_interval=3 (which is a quarter).

I need to find out if the period of ('31/03/2013' - '01/04/2010') clearly multiple of a quarter and modulus = 0. Important point is user can enter any dates and choose any interval to check. 'Day' interval fits to any dates. For 'month' and others, number of days, minutes, seconds are varying. So we can not use the fixed values for them neither we can use '1 month - 1 day' or any interval in division. Also we can not cast them to integers.

How to get it done?

First, you need to define, in a way that meets all your business/system/financial/whatever requirements, exactly what each interval means. The way PostgreSQL interprets certain ambiguities may be different than what you need. It has also changed over time. Many versions back, for instance, subtracting two dates that crossed a daylight saving time change would give you 23 or 25 hours but now returns 1 day. You also have to have a good understanding of when and how casting takes place and how the different data-types behave:

steve@[local] => select '2013-03-11'::timestamptz - '2013-03-10'::timestamptz;
 ?column?
----------
 23:00:00


steve@[local] => select '2013-03-11'::date - '2013-03-10'::date;
 ?column?
----------
        1

Same thing with operator precedence and ordering:

steve@[local] => select '2013-03-31'::date - '1 month'::interval + '1 month'::interval;
      ?column?
---------------------
 2013-03-28 00:00:00

steve@[local] => select '2013-03-31'::date + '1 month'::interval - '1 month'::interval;
      ?column?
---------------------
 2013-03-30 00:00:00

Is the end of one month to the end of the next month one-month? And can the user reverse the dates? If end-of-February (2013-02-28) is one month before end-of-March (2013-03-31) then what is the span of time between Feb 28 and March 28? 29? 30?

Before anyone can help with implementation you need to provide a detailed definition of your operations including special cases.

Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux