Strange? BETWEEN behaviour.

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

--

William Ivanski

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?

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

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

--
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

--
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, 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 

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

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?

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

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.

--
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, 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.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.

--
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 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.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
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, 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?

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