Обсуждение: interval and timestamp change?

this works on  postgresql 7.1.3.

SELECT interval(('1/30/02  12:30 pm'::timestamp) - 'now');

However, it is failing on the current beta. I have to change it to:

SELECT ('1/30/02  12:30 pm'::timestamp) - 'now';

Is this change to make PG more ANSI compliant? Was this 
documented? It  took me a bit of hacking to find the correct syntax in 
7.2.

Ted

Ted,

Tom (Lane) told me a while ago that they changed timestamp and time 
to be SQL92 compliant and they changed the way this now works.

Here is Tom's explanation from that time:
TIME is a reserved word now, or at least more reserved than it used to
be.  You'll need to write 'now'::time or CAST('now' AS time).  Sorry
about that, but SQL92 requires it...

Best regards,
Chris

At 19:03 -0500 01/26/2002, postgresql wrote:
>this works on  postgresql 7.1.3.
>
>SELECT interval(('1/30/02  12:30 pm'::timestamp) - 'now');
>
>However, it is failing on the current beta. I have to change it to:
>
>SELECT ('1/30/02  12:30 pm'::timestamp) - 'now';
>
>Is this change to make PG more ANSI compliant? Was this
>documented? It  took me a bit of hacking to find the correct syntax in
>7.2.
>
>Ted
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org


-- 
Chris Ruprecht
Network grunt and bit pusher extraordinaíre

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

[2002-01-26 18:46] Chris Ruprecht said:
| Ted,
| 
| Tom (Lane) told me a while ago that they changed timestamp and time 
| to be SQL92 compliant and they changed the way this now works.
| 
| Here is Tom's explanation from that time:
| TIME is a reserved word now, or at least more reserved than it used to
| be.  You'll need to write 'now'::time or CAST('now' AS time).  Sorry
| about that, but SQL92 requires it...

'NOW' is not a standard keyword.  The /standard/ keywords for 
current time (and company) are: CURRENT_TIMESTAMP CURRENT_TIME CURRENT_DATE LOCALTIME         *** not implemented in
postgresqlLOCALTIMESTAMP    *** not implemented in postgresql
 

You should be using one of the above supported keywords instead
of 'NOW'.

cheers. brent

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

Chris Ruprecht <chrup999@yahoo.com> writes:
> Here is Tom's explanation from that time:
> TIME is a reserved word now, or at least more reserved than it used to
> be.  You'll need to write 'now'::time or CAST('now' AS time).  Sorry
> about that, but SQL92 requires it...

Actually there's nothing wrong with his 'now', though as Brent points
out CURRENT_TIMESTAMP would be more standards-compliant.  The real
problem is that INTERVAL is also a more-reserved word than it used to
be.  So instead of

SELECT interval(('1/30/02  12:30 pm'::timestamp) - 'now');
ERROR:  parser: parse error at or near "("

he needs

SELECT "interval"(('1/30/02  12:30 pm'::timestamp) - 'now');

or

SELECT (('1/30/02  12:30 pm'::timestamp) - 'now')::interval;

although since the difference of two timestamps is already an interval,
there's not really any need for the cast anyway.  So this is sufficient:

SELECT '1/30/02  12:30 pm'::timestamp - current_timestamp;
        regards, tom lane