Re: date ranges in where

On May 6, 2009, at 2:55 PM, Miguel Miranda wrote:

>
> On Wed, May 6, 2009 at 3:51 PM, Erik Jones <ejones(at)engineyard(dot)com>
> wrote:
>
> On May 6, 2009, at 2:48 PM, Miguel Miranda wrote:
>
> Well, i tried all your sugestions, and i found some funny issues, i
> use the query to count exactly in a day by day basis, and running
> the query with
>
> WHERE lastlogin >= '2009-05-01' AND lastlogin < '2009-05-03'
>
> OR
>
> WHERE lastlogin >= 'X' AND lastlogin <= 'Y' + '1 day'::interval
>
>
> it includes the 0 hours of day 3:
>
> 05-02-2009 12:00:00 AM
>
> No, 05-02-2009 12:00:00 AM is the midnight point between 2009-05-01
> and 2009-05-02.

Migeul's reply:

> sorry, i edited the date, the correct one is
>
> 05-03-2009 12:00:00 AM
>
> If i group by day, it count 1 user for day 2009-05-03 in the output,
> so it adds 1 to the total count of the range

OK, you're going to have to show me an example where that holds:

pagila=# create table test (a date);
CREATE TABLE
Time: 121.029 ms

pagila=# insert into test values ('2009-05-01'), ('2009-04-30
23:59:59'), ('2009-05-02 13:15:00'), ('2009-05-03 00:00:00');
INSERT 0 4
Time: 1.201 ms

pagila=# select count(*) from test where a >= '2009-05-01' and a <
'2009-05-03';
count
-------
2
(1 row)

Time: 0.690 ms

pagila=# select * from test where a >= '2009-05-01' and a <
'2009-05-03';
a
---------------------
2009-05-01 00:00:00
2009-05-02 13:15:00
(2 rows)

Time: 0.386 ms

P.S. Please don't top post mid-conversation, it makes it very
difficult to reply in a way that is readable with the proper context.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k