Thread: date/time problem in v6.5.3 and 7.0.0 ...
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
usingan explicit cast
template1=> select version(); version
---------------------------------------------------------------------PostgreSQL 7.0.0 on i386-unknown-freebsd4.0,
compiledby 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
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 usingan 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 Serviceand other goodies at http://donb.photo.net.
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
usingan explicit cast
José
> > 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
> > > 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
> > > > 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
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************