A query that works under 7.1 does'nt work under 7.2

[SkyHigh]

Hi

The following query works very well in 7.1 but does not work under 7.2

SELECT order_id FROM order
WHERE order_date + INTERVAL
(term ||'year') < current_timestamp;

Please let me know if there are any changes to INTERVAL in 7.2

Thanks for your help.
Brenda

 

[rycamor]

INTERVAL is really not a function. It is a datatype and an operator. In earlier version of PostgreSQL, you were allowed to 'sort of' use it as a function, but that no longer exists. Instead, try to either:

1. cast your value to an interval datatype:

   SELECT order_id FROM order
   WHERE order_date + ((term ||'year')::interval) 
      < current_timestamp;

(see

http://www.postgresql.org/idocs/index.php?sql-expressions.html#SQL-SYNTAX-TYPE-CASTS)

2. Or, you can use the &quot;interval&quot; keyword as an operator to a timestamp representation, if you remove any ambiguity about the parentheses. (Meaning: if you need parentheses for your expression, then put &quot;interval&quot; in quotes:

   SELECT order_id FROM order
   WHERE order_date + ("interval" (term ||'year')) 
      < current_timestamp;

(see

http://www.postgresql.org/idocs/index.php?functions-datetime.html

and

http://www.postgresql.org/idocs/index.php?functions-formatting.html)

I can't vouch for whether your query is correctly formed, though, since I don't know the table structure. But, I suggest you use more parentheses to remove ambiguity from your queries:

   SELECT order_id FROM order
   WHERE (order_date + ("interval" (term ||'year'))) 
      < current_timestamp;

By the way, the reason for the changed behavior about parentheses affects all date/time-related expressions: they all have an optional argument (p), which can be any number from 0 to 13 specifying the precision you want from that value. (

http://www.postgresql.org/idocs/index.php?datatype-datetime.html).

So, your above query could be written:

   SELECT order_id FROM order
   WHERE (order_date + (interval (9) (term ||'year'))) 
      < current_timestamp;

Thus, another thing to watch for now is that all timestamp, timestamptz, and interval expressions will default to full precision, which could mean you will see values like

2001-06-21 08:43:36.391869+02

instead of

2001-06-21 08:43:36+02

. -------------------------------------------

Big Brother: &quot;War is Peace&quot; -- Big Business: &quot;Trust is Suspicion&quot;
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

 

[rycamor]

With apologies, I have to note that so far I have been unable to get PostgreSQL to recognize the precision argument with 'interval (p)'. I don't know if it is a bug, or I am just missing something, but I will research it more. -------------------------------------------

Big Brother: &quot;War is Peace&quot; -- Big Business: &quot;Trust is Suspicion&quot;
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

 

[rycamor]

OK, so far here is the best I can do with the precision modifier on intervals:

Notice the difference in responses between

sweb=# SELECT interval(6) '647 days 15:16:58.7991779968';                                                                         
           interval           
------------------------------
 647 days 15:16:58.7991779968
(1 row)

and

postgres=# SELECT interval(0) '647 days 15:16:58.7991779968';                                                                         
     interval      
-------------------
 647 days 15:16:59
(1 row)

Still trying to examine for syntax that can handle more complex expressions, but the documentation is not very helpful on this point. -------------------------------------------

Big Brother: &quot;War is Peace&quot; -- Big Business: &quot;Trust is Suspicion&quot;
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

 

[SkyHigh]

Hi rycamor

This is great, thanks very much for your help
Brenda