Обсуждение: Strange? BETWEEN behaviour.

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?


Regards,

BTJ

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen

btj@havleik.no
-----------------------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
-----------------------------------------------------------------------------------------------

On 2016-10-20 13:51, Bjørn T Johansen 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?
>
>
> Regards,
>
> BTJ
>

What are you getting?

Yes, that's what I am doing now but I was just wondering why the other way did not work...

BTJ

On Thu, 20 Oct 2016 12:02:14 +0000
William Ivanski <william.ivanski@gmail.com> wrote:

> You could try:
>
> select * from table where date = '2016/10/20'::date
>
> Em qui, 20 de out de 2016 às 09:52, Bjørn T Johansen <btj@havleik.no>
> escreveu:
>
> > 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?
> >
> >
> > Regards,
> >
> > BTJ
> >
> > --
> >
> > -----------------------------------------------------------------------------------------------
> > Bjørn T Johansen
> >
> > btj@havleik.no
> >
> > -----------------------------------------------------------------------------------------------
> > Someone wrote:
> > "I understand that if you play a Windows CD backwards you hear strange
> > Satanic messages"
> > To which someone replied:
> > "It's even worse than that; play it forwards and it installs Windows"
> >
> > -----------------------------------------------------------------------------------------------
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >

On Thu, 20 Oct 2016 14:04:51 +0200
vinny <vinny@xs4all.nl> wrote:

> On 2016-10-20 13:51, Bjørn T Johansen 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?
> >
> >
> > Regards,
> >
> > BTJ
> >
>
> What are you getting?
>
>

The sql returns 5 of the expected 72 rows...

BTJ

On Thu, 2016-10-20 at 13:51 +0200, Bjørn T Johansen 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?
>
>
> Regards,
>
> BTJ
>
>


I assume that you can use full stops as date separators.

Anyway, this will work:-


SELECT * from table WHERE TO_CHAR(date,'YYYYMMDD') = '20161020';


HTH,
Rob 

On 2016-10-20 14:27, Bjørn T Johansen wrote:
> On Thu, 20 Oct 2016 14:04:51 +0200
> vinny <vinny@xs4all.nl> wrote:
>
>> On 2016-10-20 13:51, Bjørn T Johansen 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?
>> >
>> >
>> > Regards,
>> >
>> > BTJ
>> >
>>
>> What are you getting?
>>
>>
>
> The sql returns 5 of the expected 72 rows...
>
> BTJ

Sure, but what I meant was more like: what data do you have in the
records, which type, and which values are not getting through?

Can you reproduce the problem in a simple example?

Bottom quoting makes it difficult to reply properly, so reformated a bit:

On Thu, Oct 20, 2016 at 2:02 PM, William Ivanski
<william.ivanski@gmail.com> wrote:
> Em qui, 20 de out de 2016 às 09:52, Bjørn T Johansen <btj@havleik.no>
> escreveu:
>> date is of type timestamp.
> select * from table where date = '2016/10/20'::date

I think is the other way round ( date::date = '2016/10/20' ).

To me it seems yours will do:
date = '2016/10/20'::date::timestamp ( = 2016/10/20 00:00:00 )
( widening conversion )

Francisco Olarte.

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.

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

Bjørn:

On Thu, Oct 20, 2016 at 4:13 PM, Bjørn T Johansen <btj@havleik.no> wrote:

> Yes, the field name is actually dato but changed it to the English variant..

I routinely have databases with english column names for nearly
everything except 'fecha', spanish for date, and 'tipo', spanish for
type which sometimes collides, and things like 'tabla' and 'columna' (
those are easy to translate ) to avoid this kind of problems.

Anyway, when having problems try to just cut & paste the code as it
failes, because something the subtle detail is precisely what is 'lost
in translation'. Unless you abuse things like ø or ö or things like
these people do not normally have problem running them ( in spanish we
just have to avoid tildes in vowels and ñ and are fine ).

Francisco Olarte.

On Thu, Oct 20, 2016 at 6:27 AM, Bjørn T Johansen <btj@havleik.no> wrote:
> On Thu, 20 Oct 2016 14:04:51 +0200
> vinny <vinny@xs4all.nl> wrote:
>
>> On 2016-10-20 13:51, Bjørn T Johansen 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?
>> >
>> >
>> > Regards,
>> >
>> > BTJ
>> >
>>
>> What are you getting?
>>
>>
>
> The sql returns 5 of the expected 72 rows...

Could you be running into timezone issues?