Search Postgresql Archives

Re: Strange? BETWEEN behaviour.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, 20 Oct 2016 15:16:20 +0200
Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:

> On Thu, Oct 20, 2016 at 1:51 PM, Bjørn T Johansen <btj@xxxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux