Re: Query not producing expected result

On 5/2/19 12:57 AM, Francisco Olarte wrote:
> Adrian:
>
> On Wed, May 1, 2019 at 8:14 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com