On May 6, 2009, at 2:55 PM, Miguel Miranda wrote:
On Wed, May 6, 2009 at 3:51 PM, Erik Jones <ejones@xxxxxxxxxxxxxx>
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
