#1
  1. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61

    Post INTERVAL and other date/time gotchas


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

    1. cast your value to an interval datatype:
    Code:
       SELECT id FROM orders
       WHERE (current_timestamp - order_date) < ('1 month')::interval;
    (see http://www.postgresql.org/idocs/inde...TAX-TYPE-CASTS)

    2. Or, you can use the "interval" keyword as an operator to a timestamp representation:
    Code:
       SELECT id FROM orders
       WHERE (current_timestamp - order_date) < (interval '1 month 3 days');
    Also, 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:

    Code:
       SELECT id FROM orders
       WHERE (current_timestamp - order_date) < ("interval" (current_timestamp - 
          (SELECT order_date FROM orders WHERE id = 2)));
    (see http://www.postgresql.org/idocs/inde...-datetime.html and http://www.postgresql.org/idocs/inde...ormatting.html)

    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/inde...-datetime.html). Compare the output of the following two queries:

    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)
    Unfortunately, I have not yet been able to get the precision argument to work with more complex expressions, such as the following query. I don't know if this is a bug, or if I am missing something about the syntax:
    Code:
       SELECT id FROM orders
       WHERE (current_timestamp - order_date) < (interval(0) (current_timestamp - 
          (SELECT order_date FROM orders WHERE id = 2)));
    (doesn't work-- I have tried many variations)

    Fortunately, it does work for column definition in table creation statements, as in:
    Code:
    CREATE TABLE the_table (
    id SERIAL,
    span interval(0),
    the_date timestamptz(3)
    );
    (The 'span' column will never store microsecond precision, only seconds, but 'the_date' will store three decimal places of precision in microseconds)

    Anyway, because of all these changes, another thing to watch for now is that all timestamp, timestamptz, and interval expressions will default to full precision unless you specify otherwise. This mean syou will see values like 2001-06-21 08:43:36.391869+02 instead of 2001-06-21 08:43:36+02.

    Personally, I wish PostgreSQL would default to (0) precision in microseconds unless otherwise specified. It's great to have it available, but how often do we really need all that precision?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0

    interval ?!


    hey there!
    i have a big problem using "interval":

    the following selects won't work - why?!

    select CURRENT_TIMESTAMP + "interval" 'RS_E_DAYS DAYS' from STEPS where RS_E_DAYS > 0 LIMIT 1

    select CURRENT_TIMESTAMP + "interval" RS_E_DAYS || ' DAYS' from STEPS where RS_E_DAYS > 0 LIMIT 1

    could somebody please tell me how to add a number of days from a selected column to CURRENT_TIMESTAMP ?
    in MySQL it works fine!
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Do you get an error message? If so, what is it? what is the table structure of STEPS?

    I suspect, though, that what you need to do is explicitly cast RS_E_DAYS to text type:

    select CURRENT_TIMESTAMP + interval (RS_E_DAYS::text || ' days') from STEPS where RS_E_DAYS > 0 LIMIT 1;
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0
    yeah, i get the following error:
    ERROR: parser: parse error at or near "RS_E_DAYS" at character 38

    you can easy try that: just create a table with one integer column ("days"). then add a record with lets say "5" as "days"value... and now try to select CURRENT_TIMESTAMP + days

    c the problem: as soon as you refere to a column field in the INTERVAL expression, it returns an error
    casting the column field to text doesn't change anything
    thx for your help!
    netdog
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    OK, remember: when in doubt, cast about . (sorry... couldn't resist). Read my first post in this thread again. Rather than use interval() as a function, cast your concatenated strint to the type INTERVAL:

    select CURRENT_TIMESTAMP + (RS_E_DAYS::text || ' days')::interval from STEPS where RS_E_DAYS > 0 LIMIT 1;
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0

    yeah :)


    yeah! that works thx for your help!

IMN logo majestic logo threadwatch logo seochat tools logo