Search Postgresql Archives

Problems with filter on timestamp

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have a database that logs website requests, and I'm trying to get
all unique visitors within the last 1 minute, 5 minutes and 10
minutes.  I have a table that I have the schema for below that incude 
a field remote_ip.

When I perform the query with the filter, and the query without, the
results are the same:

dblogger=# select count(distinct(remote_ip)) from weblog_entry where
request_time>(current_time-interval '1 minute');
 count 
-------
   313
(1 row)

dblogger=# select count(distinct(remote_ip)) from weblog_entry;
 count 
-------
   316
(1 row)

dblogger=# explain select count(distinct(remote_ip)) from weblog_entry
where request_time>(current_time-interval '1 minute');
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=951.98..951.98 rows=1 width=40)
   ->  Seq Scan on weblog_entry  (cost=0.00..940.85 rows=4452 width=40)
         Filter: ((request_time)::text > ((('now'::text)::time(6) with
time zone - '00:01:00'::interval))::text)
(3 rows)

This explain plain seems to me to be saying that it's casting the
timestamp to a text type before comparing with my
current_time-interval.

If I cast request_time directly to a time(6), then it works:

dblogger=# explain select count(distinct(remote_ip)) from weblog_entry
where request_time::time(6)>(current_time-interval '1 minute');
                                                                    
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1177.00..1177.00 rows=1 width=40)
   ->  Seq Scan on weblog_entry  (cost=0.00..1165.46 rows=4612 width=40)
         Filter: (((request_time)::time(6) without time zone)::time
with time zone > (('now'::text)::time(6) with time zone -
'00:01:00'::interval))
(3 rows)

dblogger=# select count(distinct(remote_ip)) from weblog_entry where
request_time::time(6)>(current_time-interval '1 minute');
 count 
-------
    18
(1 row)

Is this the right behaviour?

I'm using Postgresql 8.0.1 on AMD64 compiled from source with -m64
-march=k8 -O3 on SuSe 9.2

Alex Turner
NetEconomist

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux