Interval to number 
Author Message
 Interval to number

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.***.com/



Fri, 05 Nov 2004 19:53:42 GMT
 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)

Quote:

> 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.***.com/

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://www.***.com/ ; | when you knock, equally spaced amid those
Usenet http://www.***.com/ | that open when you don't want them to.
Auction http://www.***.com/ | - Roger Zelazny "{*filter*} of Amber"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Sat, 06 Nov 2004 01:51:39 GMT
 Interval to number
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.

Quote:
-----Original Message-----

Sent: 20 May 2002 17:35
To: Gareth Kirwan


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)


> 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.***.com/

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://www.***.com/ ; | when you knock, equally spaced amid
those
Usenet http://www.***.com/ | that open when you don't want them to.
Auction http://www.***.com/ | - Roger Zelazny "{*filter*} of Amber"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Sat, 06 Nov 2004 02:11:24 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Converse String from dba_jobs.interval in number or minute.

2. Type Conversion (Interval - Number)

3. Number of hours from interval, how?

4. Rounding numbers to 5.0 intervals

5. Number of hours from interval, how?

6. Best way to record number of selects,update,delete's during interval

7. Interval query with missing intervals

8. NUMBER(9) UNION NUMBER(9) results in NUMBER(32,32)

9. bug in interval/extract or am I abusing interval()?

10. MDX - Define time interval

11. Getting time intervals

12. Checkpoint Interval


 
Powered by phpBB® Forum Software