Thread: Time Aggregates
Hi, I'm currently doing this: SELECT symbol, date_trunc('minute', posted), min(price), max(price), avg(price) FROM trade GROUP BY symbol, date_trunc('minute',posted); to get a list of minute-averages of trade prices. I get the feeling that this is bad form, that I should be doing this some other way. Is that the case? -itai
> I'm currently doing this: > SELECT symbol, date_trunc('minute', posted), > min(price), max(price), avg(price) > FROM trade > GROUP BY symbol, date_trunc('minute', posted); > to get a list of minute-averages of trade prices. I get the feeling > that this is bad form, that I should be doing this some other way. Is > that the case? Looks OK to me. If you are doing this *a lot* (i.e. many more queries than inserts), then you might want to set up another column which contains date_trunc('minute',posted) to avoid the calculation. Something like create table trade ( symbol text, posted timestamp, price integer, mpost timestamp ) then define a rule to update mpost when posted gets set (haven't done that part). - Thomas
> > SELECT symbol, date_trunc('minute', posted), > > min(price), max(price), avg(price) > > FROM trade > > GROUP BY symbol, date_trunc('minute', posted); Hmmm... I'm not sure how to go about doing this for, say, 5 minute intervals. Basically, I want a function: date_round( timestamp, timespan ) --> timestamp that will round the timestamp to the "nearest" timespan interval, counting back from, say, the current time. date_round( '1/1/99 15:21', '5 minutes' ) --> '1/1/99 15:20' if the current time is, say, 12:00. As I see it, this involves 1. calculating the interval between now and the timestamp;2. rounding that interval to the nearest multiple of the suppliedinterval;3. adding the rounded interval to the current time. It's step 2 that I can't quite figure out. I'm thinking about using date_part( 'epoch', ... ) and some arithmetic. Will that be OK? Has anyone done this before? -itai PS. I'll also be happy if the function is called 'date_trunc' :) PPS. I expect this query to be called much less often than inserts to the table.
"Thomas Lockhart" <lockhart@alumni.caltech.edu> replied to: "Itai Zukerman" <zukerman@math-hat.com> > > I'm currently doing this: > > SELECT symbol, date_trunc('minute', posted), > > min(price), max(price), avg(price) > > FROM trade > > GROUP BY symbol, date_trunc('minute', posted); > > to get a list of minute-averages of trade prices. I get the feeling > > that this is bad form, that I should be doing this some other way. Is > > that the case? > > Looks OK to me. If you are doing this *a lot* (i.e. many more queries > than inserts), then you might want to set up another column which > contains date_trunc('minute',posted) to avoid the calculation. Something > like > > create table trade ( > symbol text, > posted timestamp, > price integer, > mpost timestamp > ) > > then define a rule to update mpost when posted gets set (haven't done > that part). Tom, I want to have two inputs to a table -- 5 widgets at 6 dollars, nextline,7 widgets at 45 dollars, nextline 1 widget at 4...sort of thing -- and output to screen the $30, $315, $4 with those last three numbers also going back into the database. Is there any way of doing this withing the SQL, or does it have to be written in a scipt external to the database itself? Cheers, -dlj.