Thread: Help with syntax for timestamp addition

New to Postgres 7.3 from 7.0.

Am having some trouble with a query that worked in 7.0 but not in
7.3.....can't seem to figure out the syntax or find info about how to do
this anywhere.

Consider for the following query:
- 'number' is an integer
- 'procedures' is the table name
- 'date' is a timestamp
- 'numdays' is an integer

SELECT number
FROM procedures
WHERE date + numdays <= CURRENT_TIMESTAMP;

In 7.0 this works with no problem...finding all rows where the date plus
some number of days is less than the current. But in 7.3 I get:

ERROR: Unable to identify an operator '+' for types 'timestamp without
time zone' and 'integer'
You will have to retype this query using an explicit cast


I've never had to create casts before so I'm not too sure how to work
this casting into the query....keep getting various syntax errors no
matter what I try. If I try to incorporate intervals, I also get errors.
I just can't seem to find good examples in any documentation.

Any help is appreciated.

-Scott

Have a try at this syntax

SELECT number
FROM procedures
WHERE date + CAST( numdays || ' days' AS interval ) <= CURRENT_TIMESTAMP;

Patrick

> --------------------------------------------------------------------------
-----------------
> Patrick Fiche
> email : patrick.fiche@aqsacom.com
> tél : 01 69 29 36 18
> --------------------------------------------------------------------------
-----------------
>
>
>


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Scott Nixon
Sent: lundi 22 novembre 2004 14:56
To: pgsql-general@postgresql.org
Subject: [GENERAL] Help with syntax for timestamp addition


New to Postgres 7.3 from 7.0.

Am having some trouble with a query that worked in 7.0 but not in
7.3.....can't seem to figure out the syntax or find info about how to do
this anywhere.

Consider for the following query:
- 'number' is an integer
- 'procedures' is the table name
- 'date' is a timestamp
- 'numdays' is an integer

SELECT number
FROM procedures
WHERE date + numdays <= CURRENT_TIMESTAMP;

In 7.0 this works with no problem...finding all rows where the date plus
some number of days is less than the current. But in 7.3 I get:

ERROR: Unable to identify an operator '+' for types 'timestamp without
time zone' and 'integer'
You will have to retype this query using an explicit cast


I've never had to create casts before so I'm not too sure how to work
this casting into the query....keep getting various syntax errors no
matter what I try. If I try to incorporate intervals, I also get errors.
I just can't seem to find good examples in any documentation.

Any help is appreciated.

-Scott





---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match




Protected by Polesoft Lockspam
http://www.polesoft.com/refer.html

> Have a try at this syntax
>
> SELECT number
> FROM procedures
> WHERE date + CAST( numdays || ' days' AS interval ) <= CURRENT_TIMESTAMP;
>
> Patrick
>

Cool!  Thanks....that works perfectly.

-Scott

On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche
<patrick.fiche@aqsacom.com> wrote:
> Have a try at this syntax
>
> SELECT number
> FROM procedures
> WHERE date + CAST( numdays || ' days' AS interval ) <=  CURRENT_TIMESTAMP;

Just for the record you could write it like this too:
 SELECT number
 FROM procedures
 WHERE date + (numdays || ' days')::interval  <= CURRENT_TIMESTAMP;

Ian Barwick

Ian Barwick wrote:
> On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche
>
> <patrick.fiche@aqsacom.com> wrote:
> > Have a try at this syntax
> >
> > SELECT number
> > FROM procedures
> > WHERE date + CAST( numdays || ' days' AS interval ) <=
> > CURRENT_TIMESTAMP;
>
> Just for the record you could write it like this too:
>  SELECT number
>  FROM procedures
>  WHERE date + (numdays || ' days')::interval  <= CURRENT_TIMESTAMP;

Just to add to the record, the mathematically sound way to write this
query would be this:

SELECT number
FROM procedures
WHERE date + numdays * interval '1 day' <= current_timestamp;

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

> Just to add to the record, the mathematically sound way to write this
> query would be this:
>
> SELECT number
> FROM procedures
> WHERE date + numdays * interval '1 day' <= current_timestamp;


Thanks for that Peter!  That's a lot closer than what I originally
had...I didn't think about doing that but it makes sense.


Is there any advantage/disadvantages to using this method or the other?




On Mon, 2004-11-22 at 10:26, Peter Eisentraut wrote:
> Ian Barwick wrote:
> > On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche
> >
> > <patrick.fiche@aqsacom.com> wrote:
> > > Have a try at this syntax
> > >
> > > SELECT number
> > > FROM procedures
> > > WHERE date + CAST( numdays || ' days' AS interval ) <=
> > > CURRENT_TIMESTAMP;
> >
> > Just for the record you could write it like this too:
> >  SELECT number
> >  FROM procedures
> >  WHERE date + (numdays || ' days')::interval  <= CURRENT_TIMESTAMP;
>
> Just to add to the record, the mathematically sound way to write this
> query would be this:
>
> SELECT number
> FROM procedures
> WHERE date + numdays * interval '1 day' <= current_timestamp;
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/

Scott Nixon <snixon@lssi.net> writes:
> Am having some trouble with a query that worked in 7.0 but not in
> 7.3.....can't seem to figure out the syntax or find info about how to do
> this anywhere.

> SELECT number
> FROM procedures
> WHERE date + numdays <= CURRENT_TIMESTAMP;

> In 7.0 this works with no problem...

(Tries it...) The reason it worked in 7.0 was that 7.0 would implicitly
down-convert the timestamp value to a value of type date, and then apply
the date-plus-integer operator.  The operator is still there, but later
versions are less willing to apply information-losing type coercions
implicitly.  So the exact equivalent of what you were doing before is

... WHERE CAST(date AS date) + numdays <= CURRENT_TIMESTAMP;

The comparison portion of this will require an up-conversion from date
back to timestamp, which is inefficient and pointless (not to mention
that it exposes you to daylight-savings-transition issues, because
CURRENT_TIMESTAMP is timestamp with time zone).  So I think what you
probably *really* want is

... WHERE CAST(date AS date) + numdays <= CURRENT_DATE;

which keeps both the addition and the comparison as simple date
operations with no sub-day resolution and no timezone funnies.

            regards, tom lane

> So I think what you probably *really* want is
>
> ... WHERE CAST(date AS date) + numdays <= CURRENT_DATE;


Thanks Tom!  Yeah, I guess you are right on that point.  I hadn't thought about that.
The implementation of this wouldn't be affected since this query is buried in a script
that runs out of cron once a day, but I suppose I might as well do it right if I'm
going to do it.



On Mon, 2004-11-22 at 11:31, Tom Lane wrote:
> Scott Nixon <snixon@lssi.net> writes:
> > Am having some trouble with a query that worked in 7.0 but not in
> > 7.3.....can't seem to figure out the syntax or find info about how to do
> > this anywhere.
>
> > SELECT number
> > FROM procedures
> > WHERE date + numdays <= CURRENT_TIMESTAMP;
>
> > In 7.0 this works with no problem...
>
> (Tries it...) The reason it worked in 7.0 was that 7.0 would implicitly
> down-convert the timestamp value to a value of type date, and then apply
> the date-plus-integer operator.  The operator is still there, but later
> versions are less willing to apply information-losing type coercions
> implicitly.  So the exact equivalent of what you were doing before is
>
> ... WHERE CAST(date AS date) + numdays <= CURRENT_TIMESTAMP;
>
> The comparison portion of this will require an up-conversion from date
> back to timestamp, which is inefficient and pointless (not to mention
> that it exposes you to daylight-savings-transition issues, because
> CURRENT_TIMESTAMP is timestamp with time zone).  So I think what you
> probably *really* want is
>
> ... WHERE CAST(date AS date) + numdays <= CURRENT_DATE;
>
> which keeps both the addition and the comparison as simple date
> operations with no sub-day resolution and no timezone funnies.
>
>             regards, tom lane
--
______________________________________
D. Scott Nixon

           LSSi Corp.
  email:   nixon@lssi.net
    url:   http://www.lssi.net/~snixon
  phone:   (919) 466-6834
    fax:   (919) 466-6810
______________________________________