×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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 "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:

   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: "War is Peace" -- Big Business: "Trust is Suspicion"
(http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

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

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)

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

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: "War is Peace" -- Big Business: "Trust is Suspicion"
(http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

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

(OP)
Hi rycamor

This is great, thanks very much for your help
Brenda

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close