Thread: date ranges in where

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...

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 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 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
------------------------------------------------------------------