>PostgreSQL: date/time functions with INTERVAL

Posted: May 26, 2010 in PostgreSQL

>A lot of people are wondering why date/time functions are working differently in PostgreSQL 7.2 and up. For example queries like the following no longer work:

CODE:1
SELECT id FROM orders
WHERE interval(current_timestamp – order_date) < interval(‘1 month’);

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:

CODE:2 cast your value to an interval datatype:

SELECT id FROM orders
WHERE (current_timestamp – order_date) < (‘1 month’)::interval;

CODE :3 you can use the “interval” keyword as an operator to a timestamp representation:

SELECT id FROM orders
WHERE (current_timestamp – order_date) < (interval ‘1 month 3 days’);

CODE :4 it works with parentheses if you remove any ambiguity about the parentheses. (Meaning: if you need parentheses for your expression, then put “interval” in quotes:

SELECT id FROM orders
WHERE (current_timestamp – order_date) < (“interval” (current_timestamp –
      (SELECT order_date FROM orders WHERE id = 2)));

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s