[lug] postgresql query question (Nick Golder)

[Gordon Golding]

>Is there a way to query data between two timestamps based on some
>selected interval?  For example, I want to query all data for every 60
>second interval between 2004-09-10 14:00:00 and 2004-10-10 14:00:00.
>I looked at 'INTERVAL' but that appears to only show it for the first
>interval after a selected timestamp.  

What do you really want?  Do you want to see a specific factoid as of each minute, or a sum in each minute?

Like: 

1) I'm exporting this data to Excel to make a bar graph showing number of sessions which ARE ATTACHED minute by minute.  ie: STATE as of second 00
OR:
2) I want to show how many sessions start minute by minute. ie: SUM as of second 00

This is good for SUM:

> In Oracle, I'd do: >| SELECT TO_STRING( 'YYYY-mm-dd HR24:MM:00', date_column ) AS minute,
>|        SUM( factoid ), AVG( factoid )
>|   FROM table
>|   GROUP BY TO_STRING( 'YYYY-mm-dd HR24:MM:00', date_column );
>
...snip...  E.g., given that this other DB had a function "trunc_to_minute" >
>| SELECT TRUNC_TO_MINUTE( date_column ) AS minute, SUM( factoid )
>|   FROM table
>|   GROUP BY 1;
>
>Even in Oracle, a nested query could clean it up a little:
>
>| SELECT minute, SUM( factoid ), AVG( factoid )
>|   FROM ( SELECT TO_STRING( 'YYYY-mm-dd HR24:MM:00',
>|                            date_column ) AS minute,
>|                 factoid
>|            FROM table )
>|   GROUP BY minute;
*********************

For STATE as of each minute:
  
You can do a modulo thing like this, below. 

>The problem with the modulus is that it wants to operate on integers or 
>numeric types...timestamps don't qualify

;-)  Cheat... add a column that picks out of the time-stamp only seconds and only pick modulo = zero as below.  Or do the "To String" and grab only where SUBTRING(picking out the seconds portion) = 00

-- 
Gordon Golding
aka Golding the Younger DH70
gordongoldin at netscape.net
http://cslr.colorado.edu/beginweb/cgi-bin/gen_page.php?user=goldingg&&group=STF
303-494-5730
"If Love is Blind, why is lingerie so popular?"


__________________________________________________________________
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp