Adrian:
On Wed, May 1, 2019 at 8:14 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.
