From: | Bjørn T Johansen <btj(at)havleik(dot)no> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Strange? BETWEEN behaviour. |
Date: | 2016-10-20 14:13:54 |
Message-ID: | 20161020161354.5c6d2b3d@arch-btj.norsvin.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 20 Oct 2016 15:16:20 +0200
Francisco Olarte <folarte(at)peoplecall(dot)com> wrote:
> On Thu, Oct 20, 2016 at 1:51 PM, Bjørn T Johansen <btj(at)havleik(dot)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.2016 23: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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-10-20 14:18:35 | Re: make PostgreSQL with TCLSH: No rule to make target |
Previous Message | Adrian Klaver | 2016-10-20 14:09:35 | Re: configure PostgreSQL with the python: distutils module not found |