CODEWITHSUNDEEP
sqlpostgresqlvariablestimeintervals
davviid
davviid

Reputation: 81

user-selected variable interval options in postgresql

I'm currently having trouble using the INTERVAL function in Postgresql to specify a user-selectable variable.

I know how to use INTERVAL as in

date '2001-09-28' + INTERVAL '1 hour'

but I want to allow the user to change the 1 hour as needed.

Currently, I have found that

date '2001-09-28' + INTERVAL '{{number}} hour'

works but I would love to be able to allow the timeframe (hour in this case) to be changeable by the user as well (to hour/day/week/month/etc). date '2001-09-28' + interval '{{number}} {{timeframe}}' doesn't work. I get the error

org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.

I have tried a variety of different methods, but none have yielded the answer I seek. I have tried searching for similar questions in the hopes of finding an answer, but I haven't found any.

Any help would be appreciated!

Upvotes: 2

Views: 330

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you'll need a case expression:

date '2001-09-28' + {{number}} * (case {{timeframe}}
                                      when 'hour' then interval '1 hour'
                                      when 'minute' then interval '1 minute'
                                      when 'second' then interval '1 second'
                                   end)

Upvotes: 2

Related Questions