Обсуждение: Select question

Поиск
Список
Период
Сортировка

Select question

От
"Chris Ruprecht"
Дата:
Hi all,

although not new to databases, I'm new to the wonderful world of PostGreSQl
and SQL in general.
Question:

I do this query
phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit
2 ;

And I get theis result
  cdate    | ctime | countrycode | success | carrier | duration |
phonenumber | areacode | pseq
------------+-------+-------------+---------+---------+----------+----------
---+----------+------2001-04-01 |     0 |         370 |       1 |       1 |        8 | "3703348"
| "33"     | 40052001-04-01 |     0 |          98 |       1 |       1 |       15 | "9871162"
| "71"     | 3889


Although I specified that I want only dates > 5/18/2001, I get dates
4/1/2001. Clearly, I ask the system the wrong question. How do I ask this
question the correct way?

Best regards,
Chris


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: Select question

От
Stephan Szabo
Дата:
I'm not sure, but...
Does it work if you say cdate > '2001-05-18' ?  (Possibly ::date too)
I'd guess your date value you're trying to put there is getting treated
as an integer expression.

On Wed, 23 May 2001, Chris Ruprecht wrote:

> Hi all,
> 
> although not new to databases, I'm new to the wonderful world of PostGreSQl
> and SQL in general.
> Question:
> 
> I do this query
> phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit
> 2 ;
> 
> And I get theis result
> 
>    cdate    | ctime | countrycode | success | carrier | duration |
> phonenumber | areacode | pseq
> ------------+-------+-------------+---------+---------+----------+----------
> ---+----------+------
>  2001-04-01 |     0 |         370 |       1 |       1 |        8 | "3703348"
> | "33"     | 4005
>  2001-04-01 |     0 |          98 |       1 |       1 |       15 | "9871162"
> | "71"     | 3889
> 
> 
> Although I specified that I want only dates > 5/18/2001, I get dates
> 4/1/2001. Clearly, I ask the system the wrong question. How do I ask this
> question the correct way?

Re: Select question

От
Tom Lane
Дата:
"Chris Ruprecht" <chrup999@yahoo.com> writes:
> phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit
> 2 ;

Try

select * from phonelog where cdate > '2001-05-18' order by cdate limit 2

I think it's interpreting your query as where cdate > 1978 (result of
integer subexpression) and then doing some weird integer-to-date
conversion.  In general, any constant of a non-numeric datatype needs
to be quoted in SQL queries.
        regards, tom lane