0

Is it possible to create an interval by string in PostgreSQL?

for example, I have a table:

CREATE TABLE x (
 id BIGSERIAL PRIMARY KEY,
 data JSONB
);
INSERT INTO x(data) VALUES('{"day_sess":"4_1"}');

-- the query:
SELECT DISTINCT (INTERVAL (LEFT(data->>'day_sess',1) || ' days')) FROM x ORDER BY 1;

it always give

ERROR:  syntax error at or near "LEFT"

1 Answer 1

1

Ah nevermind, found the answer

SELECT DISTINCT LEFT(data->>'day_sess',1)::INT * (INTERVAL '1 day') 
FROM x ORDER BY 1;
1
  • 1
    this is possibly the cleanest way, or you can do the concatenation as above and cast the result with ::interval Nov 11, 2015 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.