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.
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
Karl <k...@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
