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

On Wed, May 1, 2019 at 9:27 AM 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.

The field is a timestamp - which means there is a time involved. If you
don't specify one explicitly that time is going to be midnight. 9:52AM on
the 1st is after midnight on the 1st so the first query doesn't return the
9:52AM record.

IOW, the DateTime field remains as-is and the comparator is turned into a
timestamp without time zone.

David J.

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

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.

Chuck Martin
Avondale Software

On Wed, May 1, 2019 at 12:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

On 5/1/19 11:39 AM, Julien Rouhaud wrote:

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'

Wouldn't that stop the query planner from using any index on event.DateTime?

--
Angular momentum makes the world go 'round.

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.

Thanks for the extra information. It is indeed an indexed column. I'll have
to think some more about how to address this in 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.

Chuck Martin
Avondale Software

On Wed, May 1, 2019 at 1:16 PM Francisco Olarte <folarte@peoplecall.com>
wrote:

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

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.

Chuck Martin
Avondale Software

On Wed, May 1, 2019 at 1:32 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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 this in 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'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.

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

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

Ok, I see that my assumptions were incorrect. In this instance, the use of
< date+1 will return what is expected, where my solution might not have.
For other circumstances, I want to explore tsrange.

And, no, I'm not in Avondale, CA, but Decatur, GA (a few miles from
Avondale Estates, where I once lived).

Chuck Martin
Avondale Software

On Wed, May 1, 2019 at 1:52 PM Francisco Olarte <folarte@peoplecall.com>
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.

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