Lists: | pgsql-general |
---|
From: | Alex Turner <armtuk(at)gmail(dot)com> |
---|---|
To: | PgSQL General List <pgsql-general(at)postgresql(dot)org> |
Subject: | Problems with filter on timestamp |
Date: | 2005-02-02 18:52:56 |
Message-ID: | 33c6269f05020210527ace7710@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | alex(at)neteconomist(dot)com |
Cc: | PgSQL General List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problems with filter on timestamp |
Date: | 2005-02-02 21:14:58 |
Message-ID: | 6626.1107378898@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Alex Turner <armtuk(at)gmail(dot)com> writes:
> -> 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)
> 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.
Yup.
> Is this the right behaviour?
Hard to say. You did not say what the datatype of request_time is.
The whole thing looks a bit bogus though --- isn't it going to fail near
midnight due to wraparound? Seems like you should be using timestamps
not times of day.
regards, tom lane
From: | Alex Turner <armtuk(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PgSQL General List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problems with filter on timestamp |
Date: | 2005-02-03 03:31:26 |
Message-ID: | 33c6269f050202193118c60942@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Yes - I am a complete idiot:
The query is indeed completely wrong, it should be current_timestamp
not current_time. I finaly figured this out after staring at the
screen for twenty minutes trying to figure out what was going wrong.
DOH!
Alex Turner
NetEconomist
On Wed, 02 Feb 2005 16:14:58 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alex Turner <armtuk(at)gmail(dot)com> writes:
> > -> 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)
>
> > 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.
>
> Yup.
>
> > Is this the right behaviour?
>
> Hard to say. You did not say what the datatype of request_time is.
>
> The whole thing looks a bit bogus though --- isn't it going to fail near
> midnight due to wraparound? Seems like you should be using timestamps
> not times of day.
>
> regards, tom lane
>
>