On Tue, Oct 27, 2009 at 10:13:57AM -0500, Karl O. Pinc wrote:
> On 10/27/2009 08:31:45 AM, Charlie Allom wrote:
>
> > as an aside:
> > here is an SQL issue I'm having problems with..
> >
> > pmacct=> SELECT step.date,CAST(step.date as int),foo.mb FROM
> > (SELECT acct_v6.stamp_inserted,SUM(acct_v6.bytes) AS mb
> > FROM acct_v6
> > WHERE as_src = '714'
> > GROUP BY stamp_inserted) AS foo
> > RIGHT OUTER JOIN (select '2009-10-26'::timestamp + i*'10
> > minute'::interval AS date FROM generate_series(0,144) AS i) AS step
> > ON (step.date=foo.stamp_inserted) ;
> > ERROR: cannot cast type timestamp without time zone to integer
> > LINE 1: SELECT step.date,CAST(step.date as int),foo.mb FROM
>
> You can't cast dates to integers, iirc. You can add an integer
> to a date to add a certain number of days to the interval.
Oh now I understand..
> You can EXTRACT(epoch from step.date) to get a unix timestamp
> and then, e.g., use the modulo function to group into
> intervals. E.g. for ten minute intervals:
>
> group by EXTRACT(epoch from step.date)
> - (EXTRACT(epoch from step.date) % 600
'fraid it is above my head now.
pmacct=> SELECT step.date,CAST(step.date as int),foo.mb FROM
(SELECT acct_v6.stamp_inserted,SUM(acct_v6.bytes) AS mb
FROM acct_v6
WHERE as_src = '714'
GROUP BY EXTRACT(epoch from step.date) - (EXTRACT(epoch from step.date) % 600))
AS foo
RIGHT OUTER JOIN (select '2009-10-26'::timestamp + i*'10 minute'::interval AS
date FROM generate_series(0,144) AS i) AS step
ON (step.date=foo.stamp_inserted) ;
ERROR: missing FROM-clause entry in subquery for table "step"
LINE 5: GROUP BY EXTRACT(epoch from step.date) - (EXTRACT(epoch from...
^
Thanks for the advice though :) Considering throwing away the data and
using epoch seconds.
C.
--
Media Service Provider Ltd.
http://blog.playlouder.com/
http://as47998.net/
020 7729 4797
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
