Need to retrieve a range of records in one table

do you mean you want to get all records where the timestamp column is between the minimum and maximum values?

if so,  that's not really a contraint.  Every value will be between min and max.

instead, just say WHERE t.timestamp is not null

if that's not what you mean,  please elaborate.
even better,  provide sample data and expected results

Do you mean something like this?

WHERE t.timestamp between min(timestamp) and max(timestamp)

That means, you want all the records from the table. No need to include the WHERE clause.

Yes I want to retrieve all records say between 1-FEB-10 and 31-MAR-10

still need a WHERE clause to exclude NULL values

unless timestamp is constrained to be not null

>>> 1-FEB-10 and 31-MAR-10

how are those min and max?  do you mean user inputs?

if so

        SELECT t.ip a, t.signature b, count(*)
          FROM org t
         WHERE t.timestamp >= to_date('1-FEB-10','dd-MON-rr')
           AND t.timestamp <  to_date('31-MAR-10','dd-MON-rr') + 1
 GROUP BY ip, signature

this should be ok, no need another wrapping select

  SELECT   t.ip a, t.signature b, COUNT (1)
    FROM   org t
   WHERE   t.timestamp BETWEEN starting_timestamp AND ending_timestamp
GROUP BY   ip, signature

note  the >= on the lower bound  and <  on the upper bound with + 1

this is because

31-MAR-2010  13:00:00  > 31-MAR-2010

since a date without a visible time is really a date rounded down to 00:00:00

so, to include all of March 31,  
instead check to see if the data is less than April 1  

>>>  BETWEEN starting_timestamp AND ending_timestamp


This has the problem of dropping the end day as noted above

however,  if your timestamp will always have all values truncated to midnight (00:00:00)  then between will work

or as with the previous post,  using <=  but not adding 1

t.timestamp <=  to_date('31-MAR-10','dd-MON-rr')

sdstuber..........
how are those min and max?  do you mean user inputs?

if so

        SELECT t.ip a, t.signature b, count(*)
          FROM org t
         WHERE t.timestamp >= to_date('1-FEB-10','dd-MON-rr')
           AND t.timestamp <  to_date('31-MAR-10','dd-MON-rr') + 1
 GROUP BY ip, signature



These are not exactly user inputs - this script is a part of a perl script that I am trying to adjust to do this.
The perl script is meant to pull one weeks worth of data from this table and create a pdf report that get shoved into the database.  I have not been able to get any query to work inside this perl script to pull out the needed data as I am not very familiar with perl.

"user"  -real user, or "user" a script, either way.

they are inputs.  they are not values derived from the data , right?

Correct - they are inputs

SELECT ip , signature , count(*)
             FROM event_266
            WHERE timestamp >= to_date('1-FEB-10','DD-MON-YY')
             AND timestamp <  to_date('31-MAR-10','DD-MON-YY') + 1
    GROUP BY ip, signature;

returns no data even though there is data to be retrieved.

are you sure there is data?

what query did you use to verify?

also,  is timestamp  a "date" or "timestamp" type? or is it a string?

just curious,  did you intend to use 2010 data? or 2011?

also  YY or (RR) invites reinventing the Y2K bug  
I recommend using 4 digits years and YYYY format

timestamp is a date field

I have verified that there is data from January 2010 through April 14 2010
Just changed the query to use the YYYY but still no data returned.

If I take out......... the second line I get back 2422 record............in sqlplus but none if I leave the second line in

SELECT ip , signature , count(*)
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
                 GROUP BY ip, signature;

what does this return?

SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
and rownum < 20

oops, try this instead, sorry


select * from (
SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
order by ts)
and rownum < 20

That returns 19 rows

what is the output?

SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
   and rownum < 20   3    4  ;

TS
-------------------
2011-03-19 13:00:26
2011-03-19 13:39:58
2011-03-19 13:39:58
2011-03-19 13:40:01
2011-03-19 13:46:03
2011-03-19 13:58:11
2011-03-19 14:13:01
2011-03-19 14:13:05
2011-03-19 14:31:23
2011-03-19 15:11:10
2011-03-19 15:11:10
2011-03-19 15:13:24
2011-03-19 15:17:03
2011-03-19 16:10:06
2011-03-19 16:11:45
2011-03-19 17:02:57
2011-03-19 17:02:57
2011-03-19 18:30:54
2011-03-19 18:30:54

19 rows selected.


SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
     WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
    order by ts)
and rownum < 20   4    5    6  ;
and rownum < 20
*
ERROR at line 6:
ORA-00933: SQL command not properly ended

you left off part of the 2nd query

try it again, with the whole thing.

based on the results of the first query you have 2011 data, but your date range above is for 2010 as noted in http:#35507152

the 2nd query will determine absolutely if that's the problem or not