On 5/2/19 12:57 AM, Francisco Olarte wrote:
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.
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@xxxxxxxxxxx
