Re: Strange? BETWEEN behaviour.

On Thu, Oct 20, 2016 at 1:51 PM, Bjørn T Johansen <btj@havleik.no> wrote:
> I have the following SQL:
> SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 00:00:00','DD.MM.YYYY HH24:MI:SS') AND
to_timestamp('20.10.201623:59:59','DD.MM.YYYY 
> HH24:MI:SS')
> date is of type timestamp.
> I was expecting to get all the records that had datepart = 20.10.2016 but I am not getting that..
> What am I missing?

As it has been pointed, show your data, show your expected but missing values.

Besides, some general comments.

Is this a real query? Because date is a reserved word ( and gives
problems in many places )... a quick test shows it works in this
context, but using identifiers as column names has bitten me before.

Whenever you are trying to get intervals on a dataype which models a
real number ( like timestamp, which is like a point in the line of
time ) is better to always use half-open intervals ( because they can
cover the line, unless closed and open ones ). ( It's not the same for
dates, which model a day, an integer, countable number ).

This means, instead of your query prefer to use:

SELECT * from table
WHERE date >= to_timestamp('20.10.2016 00:00:00','DD.MM.YYYY HH24:MI:SS')
  AND date <  to_timestamp('21.10.2016 00:00:00','DD.MM.YYYY HH24:MI:SS')

This even let's you write the query for a single day in a very clean way:

SELECT * from table
WHERE date >= '2010-10-20'::date
  AND date <  '2010-10-20'::date + '1 day'::interval

I have to made a lot of queries for ts ( really tstz ) ranges @work
and this helps a lot.

Second advise, test your queries piecewise. If you test your constants:

n=> select to_timestamp('20.10.2016 00:00:00','DD.MM.YYYY
HH24:MI:SS'),to_timestamp('20.10.2016 23:59:59','DD.MM.YYYY
HH24:MI:SS');
      to_timestamp      |      to_timestamp
------------------------+------------------------
 2016-10-20 00:00:00+02 | 2016-10-20 23:59:59+02
(1 row)

You'll see you are building timestamp WITH time zone, not plain
timestamps. I think this is not going to have influence in your
queries, but better convert explicitly ( as it can bite you in some
ocasions ).

Francisco Olarte.