Problems with filter on timestamp

Started by Alex Turnerabout 21 years ago3 messagesgeneral
Jump to latest
#1Alex Turner
armtuk@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alex Turner (#1)
Re: Problems with filter on timestamp

Alex Turner <armtuk@gmail.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

#3Alex Turner
armtuk@gmail.com
In reply to: Tom Lane (#2)
Re: Problems with filter on timestamp

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

Show quoted text

On Wed, 02 Feb 2005 16:14:58 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alex Turner <armtuk@gmail.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