
I can add days to now(), but not subtract? 

=====================================

template1=> select now() + '30 days';
           ?column?           
------------------------------
 Sun Feb 13 22:00:33 2000 AST
(1 row)

template1=> select now() - '30 days';
ERROR:  Unable to identify an operator '-' for types 'timestamp' and 'unknown'
        You will have to retype this query using an explicit cast
template1=> select version();
                               version                               
---------------------------------------------------------------------
 PostgreSQL 7.0.0 on i386-unknown-freebsd4.0, compiled by gcc 2.95.2
(1 row)




Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Fri, 14 Jan 2000 22:02:39 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
date/time problem in v6.5.3 and 7.0.0 ...
At 10:02 PM 1/14/00 -0400, The Hermit Hacker wrote:
>
>I can add days to now(), but not subtract? 
>
>=====================================
>
>template1=> select now() + '30 days';
>           ?column?           
>------------------------------
> Sun Feb 13 22:00:33 2000 AST
>(1 row)
>
>template1=> select now() - '30 days';
>ERROR:  Unable to identify an operator '-' for types 'timestamp' and
'unknown'
>        You will have to retype this query using an explicit cast
>template1=> select version();

donb=> select now()-'30 days'::reltime;
?column?              
----------------------
1999-12-15 18:13:18-08
(1 row)

donb=> select now()-'30 days';
ERROR:  Unable to identify an operator '-' for types 'timestamp' and 'unknown'
        You will have to retype this query using an explicit cast
donb=> 

As a relative newcomer, I too have found dates a bit confusing and
my solution has been to cast like crazy rather than guess what will
happen if I don't :)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Fri, 14 Jan 2000 18:12:56 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] date/time problem in v6.5.3 and 7.0.0 ...


The Hermit Hacker wrote:

> I can add days to now(), but not subtract?
>
> =====================================
>
> template1=> select now() + '30 days';
>            ?column?
> ------------------------------
>  Sun Feb 13 22:00:33 2000 AST
> (1 row)
>
> template1=> select now() - '30 days';
> ERROR:  Unable to identify an operator '-' for types 'timestamp' and 'unknown'
>         You will have to retype this query using an explicit cast
> template1=> select version();
>                                version
> ---------------------------------------------------------------------
>  PostgreSQL 7.0.0 on i386-unknown-freebsd4.0, compiled by gcc 2.95.2
> (1 row)
>
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
>
> ************

Try using standard names (TIMESTAMP instead of now() and INTERVA) as in:

hygea=> select current_timestamp + interval '30 day';
?column?
--------------------------
17/02/2000 16:00:28.00 CET
(1 row)

hygea=>  select current_timestamp - interval '30 day';
?column?
--------------------------
19/12/1999 16:00:44.00 CET
(1 row)

hygea=> select now() - '30 day';
ERROR:  Unable to identify an operator '-' for types 'timestamp' and 'unknown'
        You will have to retype this query using an explicit cast

Josï¿½



Tue, 18 Jan 2000 15:10:58 +0100
Jose Soares <jose@sferacarta.com>
Re: [HACKERS] date/time problem in v6.5.3 and 7.0.0 ...
> > I can add days to now(), but not subtract?

The problem is that it is meaningful to subtract two absolute times,
giving a delta time as a result, *and* it is meaningful to subtract a
delta time from an absolute time, giving another absolute time as a
result. 

So your unspecified field could be either one, and Postgres can't
decide what it should be for you ;)

The error message is intentionally vague, since by the time the
message is printed the parser has lost track of whether there were
zero candidates or too many candidates.

                      - Thomas

> > =====================================
> >
> > template1=> select now() + '30 days';
> >            ?column?
> > ------------------------------
> >  Sun Feb 13 22:00:33 2000 AST
> > (1 row)
> >
> > template1=> select now() - '30 days';
> > ERROR:  Unable to identify an operator '-' for types 'timestamp' and 'unknown'
> >         You will have to retype this query using an explicit cast

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Tue, 18 Jan 2000 14:40:27 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] date/time problem in v6.5.3 and 7.0.0 ...
> > > I can add days to now(), but not subtract?
> 
> The problem is that it is meaningful to subtract two absolute times,
> giving a delta time as a result, *and* it is meaningful to subtract a
> delta time from an absolute time, giving another absolute time as a
> result. 

It would be nice if we could decide '30 days' is a delta time because
it is not suitable for an absolute time representation. Would it be
hard?
--
Tatsuo Ishii


Wed, 19 Jan 2000 10:35:18 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] date/time problem in v6.5.3 and 7.0.0 ...
> > > > I can add days to now(), but not subtract?
> > The problem is that it is meaningful to subtract two absolute times,
> > giving a delta time as a result, *and* it is meaningful to subtract a
> > delta time from an absolute time, giving another absolute time as a
> > result.
> It would be nice if we could decide '30 days' is a delta time because
> it is not suitable for an absolute time representation. Would it be
> hard?

Hmm. I'm not sure how hard it would be. The places where types need to
be matched to operators are fairly well isolated in the parser.
However, we would need a new kind of input function which does not
throw an elog(ERROR), but rather just returns failure if the input
does not get decoded. Then, we could accumulate a list of successfully
decoded types (based on our list of candidate operators), and if that
list is of length one then we have a match.

One way to implement this would be to define an additional input
routine (which the existing input routine would use) which returns an
error rather than throwing an elog() error. Then, our parser could use
this additional routine to discriminate between the candidate types.
The input routine could be found in a similar way to our existing
"implicit coersion" code, which assumes a specific function name for
each type.

The downside to this is that we have built up one additional
assumption about the form and contents of our system tables. But,
since it adds functionality that probably isn't a bad thing.

I do know I'm not likely to find time to work on it for the next
release...

                      - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 19 Jan 2000 02:22:55 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] date/time problem in v6.5.3 and 7.0.0 ...
This table shows every legal operation between datetimes and intervals and
vice versa
allowed by SQL92:

-----------------------------------------
1st operand|operator|2nd operand|result
-----------+--------+-----------+--------
datetime   |   -    |datetime   |interval
datetime   |   +    |interval   |datetime
datetime   |   -    |interval   |datetime
interval   |   +    |datetime   |datetime
interval   |   +    |interval   |interval
interval   |   -    |interval   |interval
interval   |   *    |number     |interval
interval   |   /    |number     |interval
number     |   *    |interval   |interval
-----------+--------+-----------+--------

I wrote some pgPL/SQL functions to create operators between datetimes and
intervals.
Probably there's a better way to achieve that goal, but this works anyway.

Josï¿½



Thomas Lockhart wrote:

> > > > > I can add days to now(), but not subtract?
> > > The problem is that it is meaningful to subtract two absolute times,
> > > giving a delta time as a result, *and* it is meaningful to subtract a
> > > delta time from an absolute time, giving another absolute time as a
> > > result.
> > It would be nice if we could decide '30 days' is a delta time because
> > it is not suitable for an absolute time representation. Would it be
> > hard?
>
> Hmm. I'm not sure how hard it would be. The places where types need to
> be matched to operators are fairly well isolated in the parser.
> However, we would need a new kind of input function which does not
> throw an elog(ERROR), but rather just returns failure if the input
> does not get decoded. Then, we could accumulate a list of successfully
> decoded types (based on our list of candidate operators), and if that
> list is of length one then we have a match.
>
> One way to implement this would be to define an additional input
> routine (which the existing input routine would use) which returns an
> error rather than throwing an elog() error. Then, our parser could use
> this additional routine to discriminate between the candidate types.
> The input routine could be found in a similar way to our existing
> "implicit coersion" code, which assumes a specific function name for
> each type.
>
> The downside to this is that we have built up one additional
> assumption about the form and contents of our system tables. But,
> since it adds functionality that probably isn't a bad thing.
>
> I do know I'm not likely to find time to work on it for the next
> release...
>
>                       - Thomas
>
> --
> Thomas Lockhart                         lockhart@alumni.caltech.edu
> South Pasadena, California
>
> ************
Wed, 19 Jan 2000 14:39:59 +0100
Jose Soares <jose@sferacarta.com>
Re: [HACKERS] date/time problem in v6.5.3 and 7.0.0 ...