Extracting the number of days from a calculated interval
postgresql
Solution
SELECT EXTRACT(DAY FROM INTERVAL to_date - from_date) FROM histories;
This makes no sense. `INTERVAL xxx` is syntax for interval literals. So `INTERVAL from_date` is a syntax error, since `from_date` isn't a literal. If your code really looks more like `INTERVAL '2012-02-01'` then that's going to fail, because `2012-02-01` is not valid syntax for an `INTERVAL`.
The `INTERVAL` keyword here is just noise. I suspect you misunderstood an example from the documentation. Remove it and the expression will be fine.
I'm guessing you're trying to get the number of days between two dates represented as `timestamp` or `timestamptz`.
If so, either cast both to date:
SELECT to_date::date - from_date::date FROM histories;
or get the interval, then extract the day component:
SELECT extract(day from to_date - from_date) FROM histories;
Problem
I am trying to get a query like the following one to work: ``` SELECT EXTRACT(DAY FROM INTERVAL to_date - from_date) FROM histories; ``` In the referenced table, to_date and from_date are of type timestamp without time zone. A regular query like ``` SELECT to_date - from_date FROM histories; ``` Gives me interval results such as '65 days 04:58:09.99'. But using this expression inside the first query gives me an error: invalid input syntax for type interval. I've tried various quotations and even nesting the query without luck. Can this be done?