Обсуждение: Query not producing expected result

On Wed, May 1, 2019 at 6:27 PM Chuck Martin <clmartin@theombudsman.com> wrote:
>
> I need help figuring out why a query is not returning the records I expect it to. I'm searching on a DateTime column
(timestampwithout time zone - not nullable). The query includes:
 
>
>  AND event.Primaryresp_fkey = 5000011 AND event.DateTime <= 'May-1-2019'  AND event.EventDone < 1
>
> This does not return a record that has a DateTime value "May 1, 2019 9:52 AM". If I modify the query to:
>
>  AND event.Primaryresp_fkey = 5000011 AND event.DateTime <= 'May-2-2019'  AND event.EventDone < 1
>
> it does return the record in question. I assume I am not doing the data comparison correctly, but don't know why.

That's because it's using a timestamp comparison, not a date
comparison.  So 'May-1-2019' is casted to timestamp, which gives
2019-05-01 00:00:00.

If you want a comparison based on date instead of timestamp , you have
to ask to it explicitly.  For instance: event.DateTime::date <=
'May-1-2019'

Chuck Martin <clmartin@theombudsman.com> writes:
> I need help figuring out why a query is not returning the records I expect
> it to. I'm searching on a DateTime column (timestamp without time zone -
> not nullable). The query includes:

>  AND event.Primaryresp_fkey = 5000011 AND event.DateTime <= 'May-1-2019'
>  AND event.EventDone < 1

> This does not return a record that has a DateTime value "May 1, 2019 9:52
> AM".

Well, no, since the implied value of the constant is 'May-1-2019 00:00'.

If you only want 1-day precision of the comparison, maybe you should cast
or truncate the timestamp down to date.

            regards, tom lane

On Wed, May 1, 2019 at 6:27 PM Chuck Martin <clmartin@theombudsman.com> wrote:

I need help figuring out why a query is not returning the records I expect it to. I'm searching on a DateTime column (timestamp without time zone - not nullable). The query includes:
AND event.Primaryresp_fkey = 5000011 AND event.DateTime <= 'May-1-2019'  AND event.EventDone < 1

This does not return a record that has a DateTime value "May 1, 2019 9:52 AM". If I modify the query to:
AND event.Primaryresp_fkey = 5000011 AND event.DateTime <= 'May-2-2019'  AND event.EventDone < 1

it does return the record in question. I assume I am not doing the data comparison correctly, but don't know why.

That's because it's using a timestamp comparison, not a date
comparison.  So 'May-1-2019' is casted to timestamp, which gives
2019-05-01 00:00:00.

If you want a comparison based on date instead of timestamp , you have
to ask to it explicitly.  For instance: event.DateTime::date <=
'May-1-2019'

Chuck:

On Wed, May 1, 2019 at 6:56 PM Chuck Martin <clmartin@theombudsman.com> wrote:
> Thanks, guys. It should have been obvious to me, but wasn't.
> I found the correct result was returned with either
> AND event.DateTime <= 'May-1-2019 24:00'
> or
> AND event.DateTime::date <= 'May-1-2019'
> The latter seems best.

The latter may prevent index usage, if you've got one.

One think I've said before. Dates are integer-like ( they are
countable ), but timestamps are real-like ( they may be countable due
to finite precision, like float or doubles are, but you should not
count on it ). For real-like stuff it is normally better to work with
half-open ranges, which in your case would translate to to query for

   event.DateTime < 'May-2-2019'  ( the minus-ininity <= DateTime
would for the other end ).

The reason is you can cover the whole DateTime domain with
non-intersecting half-open ranges, but not with open or closed ones
and, as a side effect, the starting point of a range is the same as
the next one ( also, this does not need cast, better for the optimizer
) ( If your input is an end date I normally pass this to timestamp
using ts < '$inclusive_end_date'::date + 1 ) ( I do lots of reports of
this kind, and using this helps a lot once you get the hang of it
after a couple tests ).

( I use half-open for dates to, for uniformity, and for being able to
use FEBRUARY= dateColumn >= "YEAR-02-01' and dateColumn <
"YEAR-03-01", no need to worry about leap years or remembering how
many days each month has. Generally they are easier, the only con I've
found is inability to use between ).

Francisco Olarte.

On 5/1/19 10:15 AM, Francisco Olarte wrote:
> Chuck:
> 
> On Wed, May 1, 2019 at 6:56 PM Chuck Martin <clmartin@theombudsman.com> wrote:
>> Thanks, guys. It should have been obvious to me, but wasn't.
>> I found the correct result was returned with either
>> AND event.DateTime <= 'May-1-2019 24:00'
>> or
>> AND event.DateTime::date <= 'May-1-2019'
>> The latter seems best.
> 
> The latter may prevent index usage, if you've got one.
> 
> One think I've said before. Dates are integer-like ( they are
> countable ), but timestamps are real-like ( they may be countable due
> to finite precision, like float or doubles are, but you should not
> count on it ). For real-like stuff it is normally better to work with
> half-open ranges, which in your case would translate to to query for
> 
>     event.DateTime < 'May-2-2019'  ( the minus-ininity <= DateTime
> would for the other end ).
> 
> The reason is you can cover the whole DateTime domain with
> non-intersecting half-open ranges, but not with open or closed ones
> and, as a side effect, the starting point of a range is the same as
> the next one ( also, this does not need cast, better for the optimizer
> ) ( If your input is an end date I normally pass this to timestamp
> using ts < '$inclusive_end_date'::date + 1 ) ( I do lots of reports of
> this kind, and using this helps a lot once you get the hang of it
> after a couple tests ).
> 
> ( I use half-open for dates to, for uniformity, and for being able to
> use FEBRUARY= dateColumn >= "YEAR-02-01' and dateColumn <
> "YEAR-03-01", no need to worry about leap years or remembering how
> many days each month has. Generally they are easier, the only con I've
> found is inability to use between ).

Would daterange help/simplify?:

create table dt_test(id integer, dt_fld date);

insert into dt_test values (1, '2019-02-03'), (2, '2019-02-26'), (3, 
'2019-03-01');

select dt_fld from dt_test where  dt_fld  <@  daterange('2019-02-01', 
'2019-03-01');
    dt_fld
------------
  2019-02-03
  2019-02-26

> 
> Francisco Olarte.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Chuck:

On Wed, May 1, 2019 at 7:23 PM Chuck Martin <clmartin@theombudsman.com> wrote:
>
> Thanks for the extra information. It is indeed an indexed column. I'll have to think some more about how to address
thisin a general way, as this issue can come up all over. I suppose using
 
>
> AND datetime <= 'May 1, 2019 24:00'
>
> would produce the same as
>
> AND datetime < 'May 2, 2019'
>
> wouldn't it? I'm not sure one is easier to implement than the other.

At first the <= seems easier, but it is deceiving. So deceiving it is
incorrect, you should use < in both.

I've made a test:

http://sqlfiddle.com/#!17/9eecb/29310
It basically says << select 'May 1 2019 24:00'::timestamp, 'May 2
2019'::timestamp >>
Gives the same result for both, << 2019-05-02T00:00:00Z >>.

So your first condition, using <= is wrong as it will select data just
at 00:00:00 of the next day.

This is why I was telling you to use half-open-ranges, and once you
use half-open ( < ) it is easier to see whats is going on using  'May
2 2019', or 'May 1 2019'::date+1, than remembering a 24:00:00 folds to
the next day due to the peculiarities of text to timestamp conversion
( which allows just this value, but advances the date ).

Also, see that even if you use 'May 1 2019'::date+1,and index can be
used, as the casting and conversions can be constant-folded.

I normally recommend everyone to get used to half-open for time
intervals and conditions, it is much easier to get right. Also,
24:00(:00.0000000) is just one above the limit for dates know, but
earth rotation is slowing, and it may be fixed by either putting more
seconds or more hours, so who knows. But ::date+1 will be correct for
as long as people keep maintaining postgres time arithmetic routines.

Francisco Olarte.

On 5/1/19 10:37 AM, Chuck Martin wrote:
> Something like daterange would be a solution in some circumstances, but 
> this query is a user-generated one, and they don't have that much 
> control over the query. It has to be modified as needed behind the 
> scenes so that it produces the results they expect. In this instance, 
> I'm now (given the advice received here) inclined to check the value 
> entered when searching for a date, and if no time is entered, add 
> '24:00' to the date.

I should have made it clearer, my suggestion was mostly directed at 
Franciso's example.

Still:

select tsrange('2019-05-01', '2019-05-02') @> '2019-05-01 9:52'::timestamp;
  ?column?
----------
  t

If you are modifying anyway:)

For this sort of thing, I have found range types to be a time and sanity 
saver. Just throwing it out there.

> 
> Chuck Martin
> Avondale Software
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, May 1, 2019 at 7:37 PM Chuck Martin <clmartin@theombudsman.com> wrote:
>
> Something like daterange would be a solution in some circumstances, but this query is a user-generated one, and they
don'thave that much control over the query. It has to be modified as needed behind the scenes so that it produces the
resultsthey expect. In this instance, I'm now (given the advice received here) inclined to check the value entered when
searchingfor a date, and if no time is entered, add '24:00' to the date. 

What I normally do for that is, if the user must enter a date, use
$user_input::date+1 and always go to less than.

But anyway your solution with <= is incorrect. And you have made the
mistake probably because the 24:00 lead you to think postgres will
split the timestamp, compare
the date with may 1 and the time with 24:00, and that is not true. The
less-than option plus one-day add will not lead you to that error.

You can use <= with 23:59:59.9999999999999999999, will be good for
some years if leap-seconds do not bite you.

The problem is when the user enters a date, he wants a date search, so
cast(dateTime as date) <= limit. When he enters a time he does not
usually know what he is asking for ( normally when my users ask for
'May 1 23:15 they want to include up to 23:15:59, users think in
"truncate to my precision, then search inclusively" ). But they begin
to understand it when I ask "ok, twice a month bills, go 1..15 and
16..what ?", much easier to say [YYYY-MM-01 , YYYY-MM-16) and [
YYYY-MM-16, (YYYY-MM-01)+1month)

Francisco Olarte.

On 5/1/19 10:51 AM, Francisco Olarte wrote:
> On Wed, May 1, 2019 at 7:37 PM Chuck Martin <clmartin@theombudsman.com> wrote:
>>
>> Something like daterange would be a solution in some circumstances, but this query is a user-generated one, and they
don'thave that much control over the query. It has to be modified as needed behind the scenes so that it produces the
resultsthey expect. In this instance, I'm now (given the advice received here) inclined to check the value entered when
searchingfor a date, and if no time is entered, add '24:00' to the date.
 
> 
> What I normally do for that is, if the user must enter a date, use
> $user_input::date+1 and always go to less than.
> 
> But anyway your solution with <= is incorrect. And you have made the
> mistake probably because the 24:00 lead you to think postgres will
> split the timestamp, compare

You will have to explain further as I am not seeing it:

test_(postgres)# select '2019-05-01 9:52' <= '2019-05-01 24:00'::timestamp;
  ?column?
----------
  t

test_(postgres)# select '2019-05-01 24:00' <= '2019-05-01 24:00'::timestamp;
  ?column?
----------
  t

> the date with may 1 and the time with 24:00, and that is not true. The
> less-than option plus one-day add will not lead you to that error.
> 
> You can use <= with 23:59:59.9999999999999999999, will be good for
> some years if leap-seconds do not bite you.
> 
> The problem is when the user enters a date, he wants a date search, so
> cast(dateTime as date) <= limit. When he enters a time he does not
> usually know what he is asking for ( normally when my users ask for
> 'May 1 23:15 they want to include up to 23:15:59, users think in
> "truncate to my precision, then search inclusively" ). But they begin
> to understand it when I ask "ok, twice a month bills, go 1..15 and
> 16..what ?", much easier to say [YYYY-MM-01 , YYYY-MM-16) and [
> YYYY-MM-16, (YYYY-MM-01)+1month)
> 
> Francisco Olarte.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian..

On Wed, May 1, 2019 at 7:50 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> I should have made it clearer, my suggestion was mostly directed at
> Franciso's example.
...
> For this sort of thing, I have found range types to be a time and sanity
> saver. Just throwing it out there.

I've had problems with the functions, being used to the [start,end)
notation on paper. I'll look at them again.

But anyway, after so many years of not having intervals and operators,
I read "$start<= $val and $val < $end" as "$val in [$start,$end)", I
think it shares brain paths with "for(;;)" parsing to "forever /
loop". I would like to have the "$start <= $val < $end" which some
language whose name I do not remember has, for complex $vals.

Francisco Olarte.

On 5/1/19 10:58 AM, Francisco Olarte wrote:
> Adrian..
> 
> On Wed, May 1, 2019 at 7:50 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> I should have made it clearer, my suggestion was mostly directed at
>> Franciso's example.
> ...
>> For this sort of thing, I have found range types to be a time and sanity
>> saver. Just throwing it out there.
> 
> I've had problems with the functions, being used to the [start,end)
> notation on paper. I'll look at them again.

You don't have to use the functions:

test_(postgres)# select dt_fld from dt_test where dt_fld  <@ 
'[2019-02-01, 2019-03-01)'::daterange ;
    dt_fld
------------
  2019-02-03
  2019-02-26


> 
> But anyway, after so many years of not having intervals and operators,
> I read "$start<= $val and $val < $end" as "$val in [$start,$end)", I
> think it shares brain paths with "for(;;)" parsing to "forever /
> loop". I would like to have the "$start <= $val < $end" which some
> language whose name I do not remember has, for complex $vals.
> 
> Francisco Olarte.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian:

On Wed, May 1, 2019 at 7:57 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> You will have to explain further as I am not seeing it:
> test_(postgres)# select '2019-05-01 9:52' <= '2019-05-01 24:00'::timestamp;
>   ?column?
> ----------
>   t
>
> test_(postgres)# select '2019-05-01 24:00' <= '2019-05-01 24:00'::timestamp;
>   ?column?
> ----------
>   t

Because you are using two selected examples. The one with 9:52 is ok.

The last one is misleading because you are using a constant for a
particular timestamp in MAY THE SECOND wich can be written to look
like it is in MAY THE FIRST.

Rewrite it as
select '2019-05-02'::timestamp <= '2019-05-01 24:00'::timestamp;

And you'll see and out of range date selected.

This is why <= AND 24:00 are bad and misleading.

You may not have problems with 00:00:00 times, but work a bit billing
phone calls and you'll find about one in 86400 hit it ( more in my
case as traffic distribution is skewed ). Use that kind of condition
and you end up chasing why the monthly report has a dozen less calls
than the sum of the daily ones the billing guys made using excel.

Francisco Olarte.

On Wed, May 1, 2019 at 8:04 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> You don't have to use the functions:
> test_(postgres)# select dt_fld from dt_test where dt_fld  <@
> '[2019-02-01, 2019-03-01)'::daterange ;

I knew there have to be a cast syntax ( I should have said I try to
avoid casts as well ).  I will take your advice and learn more of
these, but all that operators and cast take quite a bit to learn (
specially as I do not do that much sql and I tend to forget operators
as I routinelly have to use about a dozen languages,that's why I try
to use the more common / old way ( and 30+ years of habit take a bit
to change ) ).

Thanks.
Francisco Olarte.

On 5/1/19 11:04 AM, Francisco Olarte wrote:
> Adrian:
> 
> On Wed, May 1, 2019 at 7:57 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> You will have to explain further as I am not seeing it:
>> test_(postgres)# select '2019-05-01 9:52' <= '2019-05-01 24:00'::timestamp;
>>    ?column?
>> ----------
>>    t
>>
>> test_(postgres)# select '2019-05-01 24:00' <= '2019-05-01 24:00'::timestamp;
>>    ?column?
>> ----------
>>    t
> 
> Because you are using two selected examples. The one with 9:52 is ok.
> 
> The last one is misleading because you are using a constant for a
> particular timestamp in MAY THE SECOND wich can be written to look
> like it is in MAY THE FIRST.
> 
> Rewrite it as
> select '2019-05-02'::timestamp <= '2019-05-01 24:00'::timestamp;
> 
> And you'll see and out of range date selected.

Technically it is correct as:

test_(postgres)# select '2019-05-02'::timestamp;
       timestamp
---------------------
  2019-05-02 00:00:00

which is Midnight and is both the end of one day and start of another.

It comes down to where you want to draw the line between days.


> 
> This is why <= AND 24:00 are bad and misleading.
> 
> You may not have problems with 00:00:00 times, but work a bit billing
> phone calls and you'll find about one in 86400 hit it ( more in my
> case as traffic distribution is skewed ). Use that kind of condition
> and you end up chasing why the monthly report has a dozen less calls
> than the sum of the daily ones the billing guys made using excel.
> 
> Francisco Olarte.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian:

On Wed, May 1, 2019 at 8:14 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
.....
> > select '2019-05-02'::timestamp <= '2019-05-01 24:00'::timestamp;
> > And you'll see and out of range date selected.
>
> Technically it is correct as:
> test_(postgres)# select '2019-05-02'::timestamp;
>        timestamp
> ---------------------
>   2019-05-02 00:00:00

"Tecnhnically" is not an exact term in this context, so , ok, you right.

> which is Midnight and is both the end of one day and start of another.

That's one definition. Of part a timestamp system on which timestamps
belong to either one or two date. Use it at your own risk. I prefer to
use one where, once the time zone is fixed ( as I see some tz stuff
coming ), timestamps belong to exactly one date, I've founds it avoids
problem, IANAL, YMMV, ....

> It comes down to where you want to draw the line between days.

Normally everybody wants "timestamps in 2019-04-01" to give
"2019-04-01" when converted to date.

You can try all sort of technicisms and discussions, but the fact is
if you want the simpler/original:

      dateTime::date <= '2019-05-01'::date ( second cast is implicit usually)

And you try to keep the <= but enable indexing by augmenting the
constant to 24:00

     datetime <= '2019-05-01 24:00'::timestamp ( second cast auto )

You have changed the query, and it is the 24:00 which is nearly hiding
it. Probably explain would shot it.

But if you get into the habit of working with <:

    datTime::date < '2019-05-02'   (::date implicit )

The transformation is straightforward:

   dateTime < '2019-05-02'   (::timestamp implicit )

Even if your constants come from user input and are complex,
transformation works, as

   sometimestamp::date < somedate  ( somedate supposed to be date-typed)

Works if you just switch the casting side:

   sometimestamp< sometdate::timestamp

You know it. Playing with 24:00, which IIRC is just some exception,
and is a value that is never going to be produced on output, is nice
for quickie handwritten queries, but not a path I would recommend. In
fact if you have a date in an expression it's going to be really
akward to use, you'll have to convert a date to timestamp by piping it
through text conversions. It's, IMNSHO, much better to learn to do it
in the typed world and avoid text conversions as much as possible,
they are known to be responsible for lots of problems in the computer
bussiness.

Francisco Olarte.

On 5/2/19 12:57 AM, Francisco Olarte wrote:
> Adrian:
> 
> On Wed, May 1, 2019 at 8:14 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> .....
>>> select '2019-05-02'::timestamp <= '2019-05-01 24:00'::timestamp;
>>> And you'll see and out of range date selected.
>>
>> Technically it is correct as:
>> test_(postgres)# select '2019-05-02'::timestamp;
>>         timestamp
>> ---------------------
>>    2019-05-02 00:00:00
> 
> "Tecnhnically" is not an exact term in this context, so , ok, you right.

If you want exact:

test=> select '2019-05-01 24:00'::timestamp = '2019-05-02'::timestamp;
  ?column?
----------
  t

For the reason why see below.

> 
>> which is Midnight and is both the end of one day and start of another.
> 
> That's one definition. Of part a timestamp system on which timestamps
> belong to either one or two date. Use it at your own risk. I prefer to
> use one where, once the time zone is fixed ( as I see some tz stuff
> coming ), timestamps belong to exactly one date, I've founds it avoids
> problem, IANAL, YMMV, ....
> 
>> It comes down to where you want to draw the line between days.
> 
> Normally everybody wants "timestamps in 2019-04-01" to give
> "2019-04-01" when converted to date.
> 
> You can try all sort of technicisms and discussions, but the fact is
> if you want the simpler/original:
> 
>        dateTime::date <= '2019-05-01'::date ( second cast is implicit usually)
> 
> And you try to keep the <= but enable indexing by augmenting the
> constant to 24:00
> 
>       datetime <= '2019-05-01 24:00'::timestamp ( second cast auto )
> 
> You have changed the query, and it is the 24:00 which is nearly hiding
> it. Probably explain would shot it.
> 
> But if you get into the habit of working with <:
> 
>      datTime::date < '2019-05-02'   (::date implicit )
> 
> The transformation is straightforward:
> 
>     dateTime < '2019-05-02'   (::timestamp implicit )

Unless of course you are working with something that uses the standard 
below:

test=> select '2019-05-01 24:00'::timestamp < '2019-05-02'::timestamp; 
 

  ?column? 
 

---------- 
 

  f

I know a corner case. Still what all this points out is that it comes 
down to a definition on the part of a user/institution as to how they 
are going to define the day boundary.

> 
> Even if your constants come from user input and are complex,
> transformation works, as
> 
>     sometimestamp::date < somedate  ( somedate supposed to be date-typed)
> 
> Works if you just switch the casting side:
> 
>     sometimestamp< sometdate::timestamp
> 
> You know it. Playing with 24:00, which IIRC is just some exception,

It is not just some exception it is an ISO standard:

https://en.wikipedia.org/wiki/ISO_8601#Times

"Midnight is a special case and may be referred to as either "00:00" or 
"24:00". The notation "00:00" is used at the beginning of a calendar day 
and is the more frequently used. At the end of a day use "24:00". 
"2007-04-05T24:00" is the same instant as "2007-04-06T00:00" (see 
Combined date and time representations below). "


> and is a value that is never going to be produced on output, is nice
> for quickie handwritten queries, but not a path I would recommend. In
> fact if you have a date in an expression it's going to be really
> akward to use, you'll have to convert a date to timestamp by piping it
> through text conversions. It's, IMNSHO, much better to learn to do it
> in the typed world and avoid text conversions as much as possible,
> they are known to be responsible for lots of problems in the computer
> bussiness.
> 
> Francisco Olarte.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com