Re: Query not producing expected result - Mailing list pgsql-general

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