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.