From: | "Gareth Kirwan" <gbjk(at)thermeoneurope(dot)com> |
---|---|
To: | "'Brian McCane'" <bmccane(at)mccons(dot)net> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Interval to number |
Date: | 2002-05-20 16:40:46 |
Message-ID: | 00fc01c2001d$17efd380$55eaa8c0@gbjk1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general pgsql-hackers pgsql-interfaces pgsql-odbc |
Oh :(
I'd given up waiting for a response.
Thanks though Brian ... I currently have the triggered function:
CREATE FUNCTION logSession () RETURNS opaque AS '
DECLARE
client_rate numeric(10,2);
period interval;
to_charge numeric(10,2);
BEGIN
SELECT INTO client_rate rate from clients c where c.id=OLD.client;
SELECT INTO period max(time) - min(time) FROM convs WHERE
session_id=OLD.id;
SELECT INTO to_charge (to_number(to_char(period, ''SSSS''), ''99999D99'')
/ 60 * client_rate);
INSERT INTO previous_sessions SELECT * from current_sessions c WHERE
c.id=OLD.id;
INSERT INTO logged_convs SELECT * from convs c WHERE c.session_id=OLD.id;
INSERT INTO session_logs (session_id, time, length, charge, paid) VALUES
(OLD.id,OLD.time,period, to_charge, ''false'');
RETURN OLD;
END;'
language 'plpgsql';
So I'll try to build it into that.
-----Original Message-----
From: Brian McCane [mailto:bmccane(at)mccons(dot)net]
Sent: 20 May 2002 17:35
To: Gareth Kirwan
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Interval to number
EXTRACT is your friend :)
SELECT EXTRACT(EPOCH FROM max(occurrance) - min(occurrance))::integer ;
- brian
k=# SELECT EXTRACT(EPOCH FROM now() - '2001-01-01') ;
date_part
----------------
43583467.94995
(1 row)
On Mon, 20 May 2002, Gareth Kirwan wrote:
>
> Postgres 7.2
> I have an interval selected from a max(occurance) - min(occurance) where
> bla.
> I now want to multiply this by a rate - to create a charge...
>
> If I use to_char( interval, 'SSSS');
> I will get a seconds conversion - but that works on seconds since
midnight -
> hence
> with a one day period.
>
> Are there any better ways of converting a timestamp to an integer?
>
>
> Thanks
>
> Gareth
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid
those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
From | Date | Subject | |
---|---|---|---|
Next Message | David Stanaway | 2002-05-20 17:48:23 | Re: Informacion |
Previous Message | Brian McCane | 2002-05-20 16:35:07 | Re: Interval to number |
From | Date | Subject | |
---|---|---|---|
Next Message | Cindy | 2002-05-20 17:49:48 | Re: sun solaris & postgres |
Previous Message | Patrick Welche | 2002-05-20 16:36:30 | Re: Row Locking |
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Elwyn | 2002-05-20 17:23:19 | JDBC with SSL for postgresql |
Previous Message | Brian McCane | 2002-05-20 16:35:07 | Re: Interval to number |
From | Date | Subject | |
---|---|---|---|
Next Message | Ed Schaller | 2002-05-20 19:20:42 | Re: krb5 & multiple users |
Previous Message | Brian McCane | 2002-05-20 16:35:07 | Re: Interval to number |
From | Date | Subject | |
---|---|---|---|
Next Message | Corey W. Gibbs | 2002-05-20 21:53:29 | Is there a limit on what can be returned? |
Previous Message | Brian McCane | 2002-05-20 16:35:07 | Re: Interval to number |