date ranges in where

On 06/05/2009 22:12, Miguel Miranda wrote:

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i want
toknow what users logged in for last time between 2009-05-01 and 2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work beacuse it
doesnt include who logged in 2009-05-02 15:30:00, etc...

Why not just include the time in the comparisons?

....between '2009-05-01 00:00'::timestamp
and '2009-05-02 23:59:59'::timestamp

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i
want toknow what users logged in for last time between 2009-05-01
and 2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work
beacuse it doesnt include who logged in 2009-05-02 15:30:00, etc...

WHERE lastlogin >= '2009-05-01' AND lastlogin < '2009-05-03'

or, if the values have are some unknown X and Y dates then you can do
this:

WHERE lastlogin >= 'X' AND lastlogin <= 'Y' + '1 day'::interval

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

On Wednesday 06 May 2009 2:12:47 pm Miguel Miranda wrote:

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i want
toknow what users logged in for last time between 2009-05-01 and
2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work beacuse
it doesnt include who logged in 2009-05-02 15:30:00, etc...

lastlogin between '2009-05-01' AND '2009-05-03'

--
Adrian Klaver
aklaver@comcast.net

On May 6, 2009, at 2:17 PM, Erik Jones wrote:

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

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i
want toknow what users logged in for last time between 2009-05-01
and 2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work
beacuse it doesnt include who logged in 2009-05-02 15:30:00, etc...

WHERE lastlogin >= '2009-05-01' AND lastlogin < '2009-05-03'

or, if the values have are some unknown X and Y dates then you can
do this:

WHERE lastlogin >= 'X' AND lastlogin <= 'Y' + '1 day'::interval

Woops, that last one should've been:

WHERE lastlogin >= 'X' AND lastlogin < 'Y' + '1 day'::interval

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

On May 6, 2009, at 2:19 PM, Adrian Klaver wrote:

On Wednesday 06 May 2009 2:12:47 pm Miguel Miranda wrote:

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i
want
toknow what users logged in for last time between 2009-05-01 and
2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work
beacuse
it doesnt include who logged in 2009-05-02 15:30:00, etc...

lastlogin between '2009-05-01' AND '2009-05-03'

Technically, BETWEEN is inclusive of the two values given so that
would also match '2009-05-03 00:00:00'.

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

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

The exact result are from running:

....between '2009-05-01 00:00'::timestamp
and '2009-05-02 23:59:59'::timestamp

but i select the ranges from a web form using a textbox, and right now i
dont have a java calendar at hand, i tried this with good result:

WHERE lastlogin::date BETWEEN '2009-05-01' AND '2009-05-02'

But now the query uses seq scan and not the index in lastlogin column.

Is there another way?

On Wed, May 6, 2009 at 3:17 PM, Erik Jones <ejones@engineyard.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.

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

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

regards

On Wed, May 6, 2009 at 3:51 PM, Erik Jones <ejones@engineyard.com> wrote:

On Wednesday 06 May 2009 2:51:08 pm Erik Jones 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.

The problem being that midnight is both the end of one day and the start of
another.

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

--
Adrian Klaver
aklaver@comcast.net

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

On Wed, May 6, 2009 at 3:51 PM, Erik Jones <ejones@engineyard.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

On May 6, 2009, at 2:59 PM, Adrian Klaver wrote:

On Wednesday 06 May 2009 2:51:08 pm Erik Jones 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.

The problem being that midnight is both the end of one day and the
start of
another.

Not from perspective of the database which has no concept of
midnight. My point above was simply that 2009-05-02 12:00:00 AM is
the start of the 2nd, not the 3rd.

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

Erik Jones wrote:

On May 6, 2009, at 2:19 PM, Adrian Klaver wrote:

On Wednesday 06 May 2009 2:12:47 pm Miguel Miranda wrote:

Hi, what is the recommended way to select a range of dates?

Lets say a have a table with a lastlogin (timestamp) column and i want
toknow what users logged in for last time between 2009-05-01 and
2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work
beacuse
it doesnt include who logged in 2009-05-02 15:30:00, etc...

lastlogin between '2009-05-01' AND '2009-05-03'

Technically, BETWEEN is inclusive of the two values given so that would
also match '2009-05-03 00:00:00'.

It would be kind of nice to have a right-exclusive BETWEEN. I've had a
few situations like this come up, and while it's not a big deal to do, eg:

SELECT ... WHERE x BETWEEN start_time
AND end_time + '1 day' - '0.00001 seconds'::interval;

... it'd be nicer (and less sensitive to timestamp precision issues) to
just have a BETWEEN RIGHT EXCLUSIVE or similar.

--
Craig Ringer

On 2009-05-06, Miguel Miranda <miguel.mirandag@gmail.com> wrote:

--00032557620e737136046944dbf1
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi, what is the recommended way to select a range of dates?

if you want to compare dates, use dates!

Lets say a have a table with a lastlogin (timestamp) column and i want
toknow what users logged in for last time between 2009-05-01 and 2009-05-02?

I know that a simple

where lastlogin between '2009-05-01' and '2009-05-02' doesnt work beacuse it
doesnt include who logged in 2009-05-02 15:30:00, etc...

If you are comparing dates it does.

where lastlogin::date between '2009-05-01'::date and '2009-05-02'::date

If you leave it uncast postgres will probably convert the lastlogin to
a string and produce results other than that desired and proabaly
take longer to do it too.

On 07/05/2009 12:59, Jasen Betts wrote:

where lastlogin::date between '2009-05-01'::date and '2009-05-02'::date

If you leave it uncast postgres will probably convert the lastlogin to
a string and produce results other than that desired and proabaly
take longer to do it too.

The OP was basing the query on a timestamp column, so I'd say Postgres
was casting everything to timestamp, with the results he reported.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------