I have a PostgreSQL table which has a column of the type interval which is storing a time duration in the ISO 8601 format i.e. P1D equals "1 day".
The problem I am having is that when selecting this data from the database using Knex.js the data is converted from the string P1D into a JSON object {"days":1}, if I execute the same basic select query in the command line interface I get the string P1D back, and have the option to set the style of output SET intervalStyle = iso_8601.
As best I can tell this is being doing by a dependency of Knex.js called "node-pg-types" which in turn uses "postgres-interval". In Bookshelf.js you can set a data processor, and in using the "pg" module directly you can set different type behaviours, however it's not clear at all how to modify the behaviour of Knex.js in this regard, and yet Bookshelf.js can do this and is built on Knex.js.
In short my question is how do I make Knex.js output ISO 8601 style intervals on interval columns rather than a JSON object?
It turns out that through my research jumping from one module to another, that indeed Knex.js does use "node-pg-types" to format the interval columns, and that in turn is using "postgres-interval", neither module document this well at all.
In looking into "postgres-interval" it was evident that the data returned was a JavaScript object which was being encoded into what looked like JSON, however reading the documentation on this module it actually has functions you can call to get the data in any format:
https://github.com/bendrucker/postgres-interval
interval.toPostgres() -> string Returns an interval string. This allows the interval object to be passed into prepared statements.
interval.toISO() -> string Returns an ISO 8601 compliant string.
So the answer is to append .toISO() to your code.
I will notify the developer that this particular behaviour is not well documented so they can look to improve awareness of how Knex.js passes off some of the work to other modules which also pass work off, however I wrote this self answered question so no one else has to spend countless hours trying to figure this out.