Обсуждение: Time Intervals
I've got a rather odd problem that I can't seem to solve easily with the given date manipulation functions. I've got an expiry timestamp and a renewal interval. If something has expired it gets renewed as the expiry + renewal * n Where n is the smallest number that will cause the calculation to result in the future. So if I've got a resource that is renewed by the hour and it expired last week then I need to add on enough hours so its new expiry will be up to 1 hour in the future. Only trouble is this renewal period can be anything from minutes to months and it may have expired up to 6 months ago. If I could convert the timestamp into a julian of some sort perhaps I could do the math that way. Any ideas? -Michael _________________________________________________________________ http://fastmail.ca/ - Fast Secure Web Email for Canadians
On Wed, Feb 13, 2002 at 11:53:33AM -0500, Michael Richards wrote: > I've got a rather odd problem that I can't seem to solve easily with > the given date manipulation functions. > > I've got an expiry timestamp and a renewal interval. If something has > expired it gets renewed as the expiry + renewal * n > Where n is the smallest number that will cause the calculation to > result in the future. > > So if I've got a resource that is renewed by the hour and it expired > last week then I need to add on enough hours so its new expiry will > be up to 1 hour in the future. Only trouble is this renewal period > can be anything from minutes to months and it may have expired up to > 6 months ago. > > If I could convert the timestamp into a julian of some sort perhaps I > could do the math that way. > > Any ideas? Hmm, If I undestand your problem correctly, it's actually pretty easy: you just need to see if expiry is in the past, and if it is, set it to current_timestamp + renewal_interval. If your doing the license expired detection in the frontend in a procedural language, just do a simple update. If you want to hide all that in the backend, you _still_ probably need to use a procedural language, such as pgpsql. Do you want to actually update the databse table with a new expiry, or just calculate one on the fly? Ross
Does anyone have any good tips on how to measure the time it takes to extecute an SQL-statement in psql? I'd like to use it in conjunction with EXPLAIN in order to help me determine what needs to be optimized as well as checking that my optimization actually improved matters... Best regards Torbjörn Andersson --------------------------------------------------- Embryo Communication phone: +46 (0) 31-774 39 11(00) Kungsgatan 7a fax: +46 (0)31 774 07 80 S-411 19 Göteborg mobile: 0708-30 70 04 Sweden home: http://www.embryo.se/ mail: torbjorn.andersson@embryo.se --------------------------------------------------- "Att idag tänka annorlunda än igår skiljer den vise från den envise." John Steinbeck
see explain in the docs? http://www.postgresql.org/idocs/index.php?sql-explain.html or you could use perls timing modules as well ttfn Torbjörn Andersson wrote: > > Does anyone have any good tips on how to measure the time it takes to > extecute an SQL-statement in psql? > > I'd like to use it in conjunction with EXPLAIN in order to help me determine > what needs to be optimized as well as checking that my optimization actually > improved matters... > > Best regards > > Torbjörn Andersson > --------------------------------------------------- > Embryo Communication phone: +46 (0) 31-774 39 11(00) > Kungsgatan 7a fax: +46 (0)31 774 07 80 > S-411 19 Göteborg mobile: 0708-30 70 04 > Sweden home: http://www.embryo.se/ > mail: torbjorn.andersson@embryo.se > --------------------------------------------------- > "Att idag tänka annorlunda än igår skiljer den vise från den envise." - John > Steinbeck > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
PostgreSQL has all kinds of nifty date math tools. For example,
processdata=> SELECT CURRENT_TIMESTAMP AS "NOW", CURRENT_TIMESTAMP + interval '1 hour' AS "LATER";
NOW | LATER
------------------------+------------------------2002-02-13 12:18:30-07 | 2002-02-13 13:18:30-07
(1 row)
It seems to me that what you really want isn't to add an interval
value to your expiry timestamp, but rather you need to add the
interval value to the current timestamp. The cool thing is that
intervals like '1 week', '30 days', '5 minutes' all work like you
would expect.
So when you update your records simply do something like this:
UPDATE my_table SET expiry = CURRENT_TIMESTAMP + interval '1 hour' WHERE ...
I hope this was helpful.
Jason
"Michael Richards" <michael@fastmail.ca> writes:
> I've got a rather odd problem that I can't seem to solve easily with
> the given date manipulation functions.
>
> I've got an expiry timestamp and a renewal interval. If something has
> expired it gets renewed as the expiry + renewal * n
> Where n is the smallest number that will cause the calculation to
> result in the future.
>
> So if I've got a resource that is renewed by the hour and it expired
> last week then I need to add on enough hours so its new expiry will
> be up to 1 hour in the future. Only trouble is this renewal period
> can be anything from minutes to months and it may have expired up to
> 6 months ago.
>
> If I could convert the timestamp into a julian of some sort perhaps I
> could do the math that way.
>
> Any ideas?
>
> -Michael
> _________________________________________________________________
> http://fastmail.ca/ - Fast Secure Web Email for Canadians
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
That is the trivial case. The difficulty arises when the expiry plus the interval is still less than the current timestamp. In my original description of the problem this is the 'n' part of the equation. The only solution I can think of is if I can convert a date into a number of some sort and then just use normal math on it. I really need: | now - then | | ---------- | * (period+1) + then |_ period _| That's basically find the amount of time since it's expired and determine the number of periods between the expiry and now and take the floor of it. Multiply that by the number of periods plus 1 to get the new expiry. Since there is no way to divide 2 intervals in postgres I believe I need to find a way to turn a timestamp and an interval into a number. -Michael > PostgreSQL has all kinds of nifty date math tools. For example, > > > processdata=> SELECT CURRENT_TIMESTAMP AS "NOW", > CURRENT_TIMESTAMP + interval '1 hour' AS "LATER"; > > NOW | LATER > ------------------------+------------------------ > 2002-02-13 12:18:30-07 | 2002-02-13 13:18:30-07 > (1 row) > > It seems to me that what you really want isn't to add an interval > value to your expiry timestamp, but rather you need to add the > interval value to the current timestamp. The cool thing is that > intervals like '1 week', '30 days', '5 minutes' all work like you > would expect. > > So when you update your records simply do something like this: > > UPDATE my_table SET expiry = CURRENT_TIMESTAMP + interval '1 hour' > WHERE ... > > I hope this was helpful. > > Jason _________________________________________________________________ http://fastmail.ca/ - Fast Secure Web Email for Canadians
Michael, > The only solution I can think of is if I can convert a date into a > number of some sort and then just use normal math on it. I really > need: > > | now - then | > | ---------- | * (period+1) + then > |_ period _| > > That's basically find the amount of time since it's expired and > determine the number of periods between the expiry and now and take > the floor of it. Multiply that by the number of periods plus 1 to get > > the new expiry. > > Since there is no way to divide 2 intervals in postgres I believe I > need to find a way to turn a timestamp and an interval into a number. Hmmm ... yeah, you're right. Do you need the date+time or just thedate? If the latter, you can use the DATE data type,which isinteger-based. If the former, maybe the core team needs some help implementing * and /operators for TIMESTAMP and INTERVAL ... -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Torbjörn Andersson <tobbe@embryo.se> writes:
> Does anyone have any good tips on how to measure the time it takes to
> extecute an SQL-statement in psql?
> I'd like to use it in conjunction with EXPLAIN in order to help me determine
> what needs to be optimized as well as checking that my optimization actually
> improved matters...
See "EXPLAIN ANALYZE" in 7.2.
regards, tom lane
"Michael Richards" <michael@fastmail.ca> writes:
> The only solution I can think of is if I can convert a date into a
> number of some sort and then just use normal math on it. I really
> need:
> | now - then |
> | ---------- | * (period+1) + then
> |_ period _|
Are you *sure* that's what you want? You can certainly do it that way
--- extract(epoch from timestamp), do math, convert back --- but the
above only works if the "period" is a constant number of seconds.
Intervals like "1 month" cannot be handled as above. Less obviously,
intervals like "1 day" cannot be handled that way either (think about
daylight savings transitions).
There was a thread on this same topic just recently, and I think the
conclusion was that the cleanest way to handle real-world interval
definitions is to rely on a loop around a timestamp + interval addition
operator:
while tstamp < now do tstamp := tstamp + interval;
This is trivial to program in a plpgsql function, for example, and it's
quite cheap as long as you don't let too many periods elapse between
updates.
regards, tom lane
time psql -c "select now()" now ------------------------2002-02-13 21:21:17-05 (1 row) 0.09s real 0.05s user 0.01s system At 08:14 PM 2/13/02 +0100, Torbjög==rn Andersson wrote: >Does anyone have any good tips on how to measure the time it takes to >extecute an SQL-statement in psql? > >I'd like to use it in conjunction with EXPLAIN in order to help me determine >what needs to be optimized as well as checking that my optimization actually >improved matters... > >Best regards > > >Torbjörn Andersson >--------------------------------------------------- >Embryo Communication phone: +46 (0) 31-774 39 11(00) >Kungsgatan 7a fax: +46 (0)31 774 07 80 >S-411 19 Göteborg mobile: 0708-30 70 04 >Sweden home: http://www.embryo.se/ > mail: torbjorn.andersson@embryo.se >--------------------------------------------------- >"Att idag tänka annorlunda än igår skiljer den vise från den envise." John >Steinbeck > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Den 02-02-14 03.12 skrev Frank Bax från fbax@sympatico.ca följande: > time psql -c "select now()" > now > ------------------------ > 2002-02-13 21:21:17-05 > (1 row) > > 0.09s real 0.05s user 0.01s system Thanx! Exactly what I needed. Regards Torbjörn Andersson --------------------------------------------------- Embryo Communication phone: +46 (0) 31-774 39 11(00) Kungsgatan 7a fax: +46 (0)31 774 07 80 S-411 19 Göteborg mobile: 0708-30 70 04 Sweden home: http://www.embryo.se/ mail: torbjorn.andersson@embryo.se --------------------------------------------------- "Att idag tänka annorlunda än igår skiljer den vise från den envise." John Steinbeck
Do realize that you're timing the psql frontend with this, not the postgresql backend: the only number from that with any meaning is the 'real' wallclock time, and that will be affected by other loads on either system, or the network between them. Ross On Thu, Feb 14, 2002 at 11:18:06AM +0100, Torbj?rn Andersson wrote: > Den 02-02-14 03.12 skrev Frank Bax fr?n fbax@sympatico.ca f?ljande: > > > time psql -c "select now()" > > now > > ------------------------ > > 2002-02-13 21:21:17-05 > > (1 row) > > > > 0.09s real 0.05s user 0.01s system > > Thanx! > > Exactly what I needed. > > Regards > > Torbj?rn Andersson > --------------------------------------------------- > Embryo Communication phone: +46 (0) 31-774 39 11(00) > Kungsgatan 7a fax: +46 (0)31 774 07 80 > S-411 19 G?teborg mobile: 0708-30 70 04 > Sweden home: http://www.embryo.se/ > mail: torbjorn.andersson@embryo.se > --------------------------------------------------- > "Att idag t?nka annorlunda ?n ig?r skiljer den vise fr?n den envise." ? John > Steinbeck > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster