Skip site navigation (1) Skip section navigation (2)

Re: Major upgrade advice

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: Roberto Garcia <roberto(dot)garcia(at)cptec(dot)inpe(dot)br>
Subject: Re: Major upgrade advice
Date: 2008-06-20 07:05:08
Message-ID: 200806201005.09702.achill@matrix.gatewaynet.com (view raw or flat)
Thread:
Lists: pgsql-admin
Why not simply,
SELECT * FROM xxx WHERE <timestamp_column>::date = '2008-05-20'::date;

Στις Thursday 19 June 2008 21:56:09 ο/η Roberto Garcia έγραψε:
> We changed it because 8.3 doesn't allow the operator LIKE on timestamp 
> columns. Your syntax works fine but we weren't used to use as u do. 
> There weren't any specific reason, only another way to do that.
> 
> I think when we read that operator LIKE and timestamp values were 
> incompatible we assumed that timestamp values couldn't be compared to 
> any char value, opposed as your syntax is.
> 
> We've tried to do "select * from X where <timestamp column> = 
> '2008-05-20 10:'", expecting that the result would be any minute from 10 
> o'clock (10:15, 10:30, 10:45, etc) of the specific date, but this syntax 
> retrieves only 10:00 from that date.
> 
> Tks for the new syntax.
> 
> Regards
> Roberto Garcia
> 
> Gregory S. Youngblood wrote:
> > That's a pretty substantial change.  Why did you have to make this change?
> > Was it causing syntax errors or to get better performance on those types 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' 
> > work? I could have sworn I have used that syntax in 8.2 without having 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 performance,
> > better use of indexes, etc.) for your syntax.
> >
> > Thanks,
> > Greg
> >
> > -----Original Message-----
> > From: pgsql-admin-owner(at)postgresql(dot)org
> > [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Roberto Garcia
> > Sent: Wednesday, June 18, 2008 12:01 PM
> > Cc: pgsql-admin(at)postgresql(dot)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> >= CAST('2008-05-20' as timestamp) AND
> >       <timestamp_column> < CAST('2008-05-21' as timestamp)
> >
> > Regards
> > Roberto Garcia
> >
> >
> >   
> 
> 
> Roberto Garcia
> Banco de Dados, MSc
> Fone: (12) 3186-8405 
> -- 
> A luta contra o aquecimento global depende de cada um de nós, faça sua parte, economize recursos naturais.
> -- 
> http://www.cptec.inpe.br
> http://www.inpe.br 
> 
> 



-- 
Achilleas Mantzios

In response to

Responses

pgsql-admin by date

Next:From: Jan-Ivar MellingenDate: 2008-06-20 07:10:14
Subject: Re: Major upgrade advice
Previous:From: Tena SakaiDate: 2008-06-20 05:19:55
Subject: Re: where would I find the files I need?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group