On Thu, 20 Oct 2016 15:16:20 +0200
Francisco Olarte <folarte@peoplecall.com> wrote:
> 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.
>
>
Yes, the field name is actually dato but changed it to the English variant..
Thx for your suggestions, will keep that in mind... :)
BTJ