Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id B652E2E0054
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 20 Jun 2008 10:44:54 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 66047-02
	for <pgsql-admin-postgresql.org@developer.postgresql.org>;
	Fri, 20 Jun 2008 10:44:45 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smadev.internal.net (host3.dynacom.ondsl.gr [62.103.35.211])
	by developer.postgresql.org (Postfix) with ESMTP id 80BE02E004B
	for <pgsql-admin@postgresql.org>; Fri, 20 Jun 2008 10:44:50 -0300 (ADT)
Received: from smadev.internal.net (localhost [127.0.0.1])
	by smadev.internal.net (8.14.2/8.14.2) with ESMTP id m5KDiJlv001811;
	Fri, 20 Jun 2008 16:44:19 +0300 (EEST)
	(envelope-from achill@matrix.gatewaynet.com)
Received: from localhost (localhost [[UNIX: localhost]])
	by smadev.internal.net (8.14.2/8.14.2/Submit) id m5KDiIdS001810;
	Fri, 20 Jun 2008 16:44:18 +0300 (EEST)
	(envelope-from achill@matrix.gatewaynet.com)
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: Roberto Garcia <roberto.garcia@cptec.inpe.br>
Subject: Re: Major upgrade advice
Date: Fri, 20 Jun 2008 16:44:17 +0300
User-Agent: KMail/1.9.7
Cc: pgsql-admin@postgresql.org
References: <48594429.7040001@pinpointresearch.com>
	<200806201005.09702.achill@matrix.gatewaynet.com>
	<485BAFFB.80300@cptec.inpe.br>
In-Reply-To: <485BAFFB.80300@cptec.inpe.br>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200806201644.18300.achill@matrix.gatewaynet.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200806/177
X-Sequence-Number: 29515

=CE=A3=CF=84=CE=B9=CF=82 Friday 20 June 2008 16:26:19 =CE=BF/=CE=B7 Roberto=
 Garcia =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> We have an index on the time_stamp column, if the format of argument is=20
> different from the format the index was created it is not used, then=20
> performance is decreased because a sequential scan is done instead of an=
=20
> index scan.
Then create an additional index like
CREATE INDEX tablename_tscol_date on tablename (date(tscol));
where tscol is timestamp

>=20
> Achilleas Mantzios wrote:
> > Why not simply,
> > SELECT * FROM xxx WHERE <timestamp_column>::date =3D '2008-05-20'::date;
> >
> > =CE=A3=CF=84=CE=B9=CF=82 Thursday 19 June 2008 21:56:09 =CE=BF/=CE=B7 R=
oberto Garcia =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> >  =20
> >> We changed it because 8.3 doesn't allow the operator LIKE on timestamp=
=20
> >> columns. Your syntax works fine but we weren't used to use as u do.=20
> >> There weren't any specific reason, only another way to do that.
> >>
> >> I think when we read that operator LIKE and timestamp values were=20
> >> incompatible we assumed that timestamp values couldn't be compared to=
=20
> >> any char value, opposed as your syntax is.
> >>
> >> We've tried to do "select * from X where <timestamp column> =3D=20
> >> '2008-05-20 10:'", expecting that the result would be any minute from =
10=20
> >> o'clock (10:15, 10:30, 10:45, etc) of the specific date, but this synt=
ax=20
> >> retrieves only 10:00 from that date.
> >>
> >> Tks for the new syntax.
> >>
> >> Regards
> >> Roberto Garcia
> >>
> >> Gregory S. Youngblood wrote:
> >>    =20
> >>> That's a pretty substantial change.  Why did you have to make this ch=
ange?
> >>> Was it causing syntax errors or to get better performance on those ty=
pes of
> >>> queries? Actually, now that I think about it, didn't:
> >>> select * from X where <timestamp column> between '2008-05-20 00:00:00=
' and
> >>> '2008-05-20 23:59:59'=20
> >>> work? I could have sworn I have used that syntax in 8.2 without havin=
g to
> >>> arbitrarily cast the arguments... now I'm going to have to go look. :)
> >>>
> >>> Yup, confirmed, 8.2.7 and no casting on the date arguments when I was=
 using
> >>> between.
> >>>
> >>> I'm just curious if there was a specific reason (i.e. better performa=
nce,
> >>> better use of indexes, etc.) for your syntax.
> >>>
> >>> Thanks,
> >>> Greg
> >>>
> >>> -----Original Message-----
> >>> From: pgsql-admin-owner@postgresql.org
> >>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Roberto Garcia
> >>> Sent: Wednesday, June 18, 2008 12:01 PM
> >>> Cc: pgsql-admin@postgresql.org
> >>> Subject: Re: [ADMIN] Major upgrade advice
> >>>
> >>> Just to mention one issue we had here:
> >>>
> >>> In 8.1 we did this to retrieve all data from a specific date:
> >>> SELECT * FROM xxx
> >>> WHERE <timestamp_column> LIKE '2008-05-20%'
> >>>
> >>> In 8.3 we had to change to:
> >>> SELECT * FROM xxx
> >>> WHERE <timestamp_column> >=3D CAST('2008-05-20' as timestamp) AND
> >>>       <timestamp_column> < CAST('2008-05-21' as timestamp)
> >>>
> >>> Regards
> >>> Roberto Garcia
> >>>
> >>>
> >>>  =20
> >>>      =20
> >> Roberto Garcia
> >> Banco de Dados, MSc
> >> Fone: (12) 3186-8405=20
> >> --=20
> >> A luta contra o aquecimento global depende de cada um de n=C3=B3s, fa=
=C3=A7a sua parte, economize recursos naturais.
> >> --=20
> >> http://www.cptec.inpe.br
> >> http://www.inpe.br=20
> >>
> >>
> >>    =20
> >
> >
> >
> >  =20
>=20
>=20
> Roberto Garcia
> Banco de Dados, MSc
> Fone: (12) 3186-8405=20
> --=20
> A luta contra o aquecimento global depende de cada um de n=C3=B3s, fa=C3=
=A7a sua parte, economize recursos naturais.



=2D-=20
Achilleas Mantzios