Re: [GENERAL] Strange? BETWEEN behaviour.
On Thu, Oct 20, 2016 at 6:27 AM, Bjørn T Johansenwrote: > On Thu, 20 Oct 2016 14:04:51 +0200 > vinny 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. HH24:MI:SS') AND to_timestamp('20.10.2016 >> > 23:59:59','DD.MM. >> > 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
Re: [GENERAL] Strange? BETWEEN behaviour.
Bjørn: On Thu, Oct 20, 2016 at 4:13 PM, Bjørn T Johansenwrote: > 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
Re: [GENERAL] Strange? BETWEEN behaviour.
On Thu, 20 Oct 2016 15:16:20 +0200 Francisco Olartewrote: > On Thu, Oct 20, 2016 at 1:51 PM, 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. HH24:MI:SS') AND to_timestamp('20.10.2016 > > 23:59:59','DD.MM. > > 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. HH24:MI:SS') > AND date < to_timestamp('21.10.2016 00:00:00','DD.MM. 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. > HH24:MI:SS'),to_timestamp('20.10.2016 23:59:59','DD.MM. > 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
Re: [GENERAL] Strange? BETWEEN behaviour.
On Thu, Oct 20, 2016 at 1:51 PM, Bjørn T Johansenwrote: > I have the following SQL: > SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 > 00:00:00','DD.MM. HH24:MI:SS') AND to_timestamp('20.10.2016 > 23:59:59','DD.MM. > 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. HH24:MI:SS') AND date < to_timestamp('21.10.2016 00:00:00','DD.MM. 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. HH24:MI:SS'),to_timestamp('20.10.2016 23:59:59','DD.MM. 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
Re: [GENERAL] Strange? BETWEEN behaviour.
Bottom quoting makes it difficult to reply properly, so reformated a bit: On Thu, Oct 20, 2016 at 2:02 PM, William Ivanskiwrote: > Em qui, 20 de out de 2016 às 09:52, Bjørn T Johansen > 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
Re: [GENERAL] Strange? BETWEEN behaviour.
On 2016-10-20 14:27, Bjørn T Johansen wrote: On Thu, 20 Oct 2016 14:04:51 +0200 vinnywrote: 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. HH24:MI:SS') AND to_timestamp('20.10.2016 > 23:59:59','DD.MM. > 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
Re: [GENERAL] Strange? BETWEEN behaviour.
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. HH24:MI:SS') AND to_timestamp('20.10.2016 > 23:59:59','DD.MM. > 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,'MMDD') = '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
Re: [GENERAL] Strange? BETWEEN behaviour.
On Thu, 20 Oct 2016 14:04:51 +0200 vinnywrote: > 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. HH24:MI:SS') AND to_timestamp('20.10.2016 > > 23:59:59','DD.MM. > > 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
Re: [GENERAL] Strange? BETWEEN behaviour.
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 + William Ivanskiwrote: > 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 > escreveu: > > > I have the following SQL: > > > > SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 > > 00:00:00','DD.MM. HH24:MI:SS') AND to_timestamp('20.10.2016 > > 23:59:59','DD.MM. > > 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 > > > > b...@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
Re: [GENERAL] Strange? BETWEEN behaviour.
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. HH24:MI:SS') AND to_timestamp('20.10.2016 23:59:59','DD.MM. 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
Re: [GENERAL] 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 Johansenescreveu: > I have the following SQL: > > SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 > 00:00:00','DD.MM. HH24:MI:SS') AND to_timestamp('20.10.2016 > 23:59:59','DD.MM. > 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 > > b...@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
[GENERAL] Strange? BETWEEN behaviour.
I have the following SQL: SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 00:00:00','DD.MM. HH24:MI:SS') AND to_timestamp('20.10.2016 23:59:59','DD.MM. 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 b...@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