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

SkyHigh · Nov 11, 2002

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 · Nov 11, 2002

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:

Code:

   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 "interval" 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 "interval" in quotes:

Code:

   SELECT order_id FROM order
   WHERE order_date + (&quot;interval&quot; (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:

Code:

   SELECT order_id FROM order
   WHERE (order_date + (&quot;interval&quot; (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:

Code:

   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

Code:

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

instead of

Code:

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

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

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

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

rycamor · Nov 11, 2002

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: "War is Peace" -- Big Business: "Trust is Suspicion"
(

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

rycamor · Nov 11, 2002

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

Notice the difference in responses between

Code:

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

and

Code:

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: "War is Peace" -- Big Business: "Trust is Suspicion"
(

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

SkyHigh · Nov 12, 2002

Hi rycamor

This is great, thanks very much for your help
Brenda

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

SkyHigh

Technical User

rycamor

Programmer

rycamor

Programmer

rycamor

Programmer

SkyHigh

Technical User

Similar threads

Part and Inventory Search

Sponsor