PostgreSQL Interval data type with CFQUERYPARAM

Quick tip – if you use PostgreSQL, you should look into the INTERVAL data type. It’s a great way of expressing periods of time without using actual dates and makes things like calendaring and recurring events very easy.

Unfortunately, ColdFusion’s CFQUERYPARAM doesn’t have support for the INTERVAL type so I had to hack around to figure out how to use the two together:

UPDATE table
SET someInterval = <cfqueryparam cfsqltype="cf_sql_varchar" value="#interalValue#">::interval

The ::interval will instruct Postgres to treat it as an interval. The interval value is a string representation like “2 weeks” that must be single quoted which CFQUERYPARAM takes care of for you.

Result? It just works.