Thread: Querying now()

Hello,

I have a problem I feel Im going about the wrong way but after reading
over the date/time data types documentation a few times Im still unsure
of how to do this.

Id like my application to do something like this :

SELECT now() AS currentTime WHERE currentTime < '$timestamp';

So if it returns a row Id know $timestamp is not in the past.  However
when you run this it simply says 'ERROR:  column "currentTime" does not
exist'.

Could someone offer some insight as to the correct way to accomplish this?

Thank you.
A Gilmore

A Gilmore <agilmore@shaw.ca> writes:
> Id like my application to do something like this :

> SELECT now() AS currentTime WHERE currentTime < '$timestamp';

> So if it returns a row Id know $timestamp is not in the past.  However
> when you run this it simply says 'ERROR:  column "currentTime" does not
> exist'.

Well, you could say

  SELECT 1 WHERE now() < '$timestamp';

and see if you get a row back or not; or even more directly,

  SELECT now() < '$timestamp';

and see whether you get 't' or 'f' back.

I think a lot of novices expect something like the above to work because
they are confused about the evaluation order of SQL clauses.  The
logical model for SQL SELECT commands is:

    1.  Compute table described by FROM clause.
    2.  Filter out rows that don't pass the WHERE clause.
    3.  If grouping or aggregating, compute row groups.
    4.  Filter out row groups that don't pass the HAVING clause.
    5.  Compute the SELECT output columns.
    6.  Order the result rows according to ORDER BY.

Thus, the only auxiliary clause in which it makes sense to refer to
output columns is ORDER BY.  Before that, the output row doesn't even
exist.  An example of why you'd not like this rule to be ignored is

    SELECT 1/x AS y FROM tab WHERE x != 0;

If you could refer to y in the WHERE clause then this would fail to
achieve the desired result of avoiding a divide-by-zero error.

            regards, tom lane

On Dec 20, 2004, at 9:05, A Gilmore wrote:

> Id like my application to do something like this :
>
> SELECT now() AS currentTime WHERE currentTime < '$timestamp';
>
> So if it returns a row Id know $timestamp is not in the past.  However
> when you run this it simply says 'ERROR:  column "currentTime" does
> not exist'.

AFAIK, SELECT clause aliases are not available to any other part of the
query: it's looking for a column called currentTime, but there isn't
one in the FROM clause tables. Then again, you don't even have a FROM
clause. :) In general, it's best to show the exact query you're trying
and the error messages you're getting--and if possible, reduce it to a
test case that still exhibits the problem.

You probably want something like:

SELECT foo FROM bar WHERE current_timestamp < baz;

( current_timestamp is the SQL standard spelling of the PostgreSQL
alternative now(). )

Also note that all identifiers are converted to lowercase unless
double-quoted. PostgreSQL sees currentTime as currenttime.

Hope this helps.


Michael Glaesemann
grzm myrealbox com

Tom Lane wrote:
> A Gilmore <agilmore@shaw.ca> writes:
>
>>Id like my application to do something like this :
>
>
>>SELECT now() AS currentTime WHERE currentTime < '$timestamp';
>
>
>>So if it returns a row Id know $timestamp is not in the past.  However
>>when you run this it simply says 'ERROR:  column "currentTime" does not
>>exist'.
>
>
> Well, you could say
>
>   SELECT 1 WHERE now() < '$timestamp';
>
> and see if you get a row back or not; or even more directly,
>
>   SELECT now() < '$timestamp';
>
> and see whether you get 't' or 'f' back.
>
> I think a lot of novices expect something like the above to work because
> they are confused about the evaluation order of SQL clauses.  The
> logical model for SQL SELECT commands is:
>
>     1.  Compute table described by FROM clause.
>     2.  Filter out rows that don't pass the WHERE clause.
>     3.  If grouping or aggregating, compute row groups.
>     4.  Filter out row groups that don't pass the HAVING clause.
>     5.  Compute the SELECT output columns.
>     6.  Order the result rows according to ORDER BY.
>
> Thus, the only auxiliary clause in which it makes sense to refer to
> output columns is ORDER BY.  Before that, the output row doesn't even
> exist.  An example of why you'd not like this rule to be ignored is
>
>     SELECT 1/x AS y FROM tab WHERE x != 0;
>
> If you could refer to y in the WHERE clause then this would fail to
> achieve the desired result of avoiding a divide-by-zero error.
>
>             regards, tom lane
>

That works great.  Thank you that information is very helpful.

A Gilmore