I have a table with an interval column, something like this.
CREATE TABLE validity (
window INTERVAL NOT NULL
);
Assume the value stored is 'P3DT1H' which is in iso_8601 format. When I try to read the value, it comes in regular postgres format.
3 days 01:00:00
However I want the value in iso_8601 format. How can I achieve it?
You can use SET intervalstyle query and set the style to iso_8601. Then, when you output the results, they will be in ISO 8601 format.
_, err := s.db.Exec("SET intervalstyle='iso_8601'")
res, err := s.db.Query("select interval '1d1m'")
// res contains a row with P1DT1M
If you are looking for a way to change intervalstyle for all sessions on a server level, you can update it in your configuration file:
-- connect to your psql using whatever client, e.g. cli and run
SHOW config_file;
-- in my case: /usr/local/var/postgres/postgresql.conf
Edit this file and add the following line:
intervalstyle = 'iso_8601'
In my case the file already had a commented out line with intervalstyle, and its value was postgres. You should change it and restart the service.
That way you won't have to change the style from golang each time you run a query.
so=# CREATE TABLE validity (
w INTERVAL NOT NULL
);
CREATE TABLE
so=# insert into validity values ('3 days 01:00:00');
INSERT 0 1
you probably are looking for intervalstyle
so=# set intervalstyle to iso_8601;
SET
so=# select w From validity;
w
--------
P3DT1H
(1 row)
surely it can be set per transaction/session/role/db/cluster