PostgreSQL interval error

Question

I run a query like this:

SELECT (SELECT expire_date FROM "License" WHERE license_id = 10)
      - interval '1 mon 133 days 22:19:49.191748'

And everything is well.
But when I try this:

SELECT (SELECT expire_date FROM "License" WHERE license_id = 10)
      - interval ((SELECT expire_date-now() FROM "License" WHERE license_id = 10)
                 + interval '1 months')

I get an error:

ERROR:  syntax error at or near "("
LINE 1: ...FROM "License" WHERE license_id = 10) - interval ((SELECT ex...
                                                             ^

Please help, thanks.

What are you trying to compute? Your subqueries (probably) aren't necessary. The first "interval" keyword isn't necessary. If I follow your parentheses, you appear to be computing x - ((x - NOW) + '1 month'), that is, 1 month ago whatever the value of x. — pilcrow
– pilcrow, Commented Jan 1, 2012 at 18:59

Milen A. Radev · Accepted Answer · 2012-01-01 12:44:46Z

2

My understanding is that Postgres allows the type 'string' syntax for specifying the type for simple literal constants. For anything else you have to use ...::type or CAST ( ... AS type ).

answered Jan 1, 2012 at 12:44

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahmad Over a year ago

I tried SELECT (SELECT expire_date FROM "License" WHERE license_id = 10) - interval ((SELECT expire_date-now() FROM "License" WHERE license_id = 10 ) + interval '1 months')::character varying but still get error

Milen A. Radev Over a year ago

SELECT (SELECT expire_date FROM "License" WHERE license_id = 10) -  ((SELECT expire_date-now() FROM "License" WHERE license_id = 10 ) + interval '1 months')::interval

is what you want.

Florin Ghita · Accepted Answer · 2012-01-01 13:24:19Z

1

Your first query is equivalent to:

SELECT expire_date - interval '1 mon 133 days 22:19:49.191748' as expire_date
FROM "License" 
WHERE license_id = 10

Second(though probably won't run):

SELECT expire_date - (expire_date-now() + interval '1 months') 
FROM "License" 
WHERE license_id = 10

So, better you write:

SELECT now() - interval '1 months'  
FROM "License" 
WHERE license_id = 10

answered Jan 1, 2012 at 13:24

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Comments

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-01-01 14:29:27Z

1

I think your second query is equivalent to this one. I could be wrong.

SELECT (SELECT expire_date 
        FROM "License" WHERE license_id = 10) 

     - (SELECT expire_date - now() + interval '1 months' 
        FROM "License" WHERE license_id = 10);

answered Jan 1, 2012 at 14:29

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Comments

Erwin Brandstetter · Accepted Answer · 2012-01-01 17:35:17Z

1

You can simplify to:

SELECT expire_date - (expire_date - now()) - interval '1 month'
FROM   "License"
WHERE  license_id = 10

This is valid without additional brackets because subtractions are evaluated from left to right.
_{In my first version the one necessary pair of brackets was missing, though.}

This form of the query also prevents an error in the case that license should not be unique. You would get multiple rows instead. In that case, and if you should need it, add LIMIT 1 guarantee a single value.

@Milen diagnosed the cause of the error correctly. Experiment with these statements to see:

SELECT interval (interval '1 month');   -- error
SELECT (interval '1 month')::interval;  -- no error

However, the cure is simpler. Just don't add another cast, it is redundant.

edited Jan 1, 2012 at 17:35

answered Jan 1, 2012 at 17:09

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

PostgreSQL interval error

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related