Fields with the datatype 'interval' do not show up in the table component.

[rempargo]

Introduction

Fields with the datatype 'interval' do not show up in the table component.
For some values, it shows unexpected characters

To Reproduce

Just a very simple table with id and interval column and some test data.

BEGIN;
CREATE TABLE "public"."sqlpage_bugs" (
    "id" int4 NOT NULL DEFAULT nextval('sqlpage_bugs_id_seq'::regclass),
    "my_interval" interval DEFAULT '00:00:00'::interval,
    PRIMARY KEY ("id")
);
COMMIT;

BEGIN;
CREATE SEQUENCE IF NOT EXISTS sqlpage_bugs_id_seq;
COMMIT;

BEGIN;
INSERT INTO "public"."sqlpage_bugs" ("my_interval")
VALUES 
    ('0'),
    ('1'),
    ('2'),
    ('3'),
    ('4');
COMMIT;

The sql needed for sqlpage

-- bug.sql
SELECT 
    'table'               AS component, 
SELECT
    id as id,
    my_interval as my_interval,
    FROM sqlpage_bugs;

Actual behavior

The listed table in the browser got none or strange characters in the my_interval (with datatype 'interval' column, even though the table got real values in this column.

No sqlpage errors

[2024-12-02T10:35:58.333Z INFO  actix_web::middleware::logger] 127.0.0.1 "GET /bug.sql HTTP/1.1" 200 2371 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36" 0.002695

Screenshots

Expected behavior

Showing normal values.

Version information

• OS: Running Docker (postgres:17.2-bookworm) on macOS (Apple M3)
• Database [Postgres 17.2]
• SQLPage Version : 0.30.1

[lovasoa]

Hello and welcome to sqlpage !

I cannot reproduce this issue. Could you please upgrade to the latest release and confirm whether you still observe this behavior ?

[rempargo]

I did a brew upgrade sqlpage
(didn't realise I installed sqlpage through brew).
I got now version v0.31.0
Still the same behaviour.
Also with the binary from https://github.com/sqlpage/SQLPage/releases/download/v0.31.0/sqlpage-macos.tgz
I get the same behaviour

But with this version v0.31.0 I at least get an ERROR in the console.

[2024-12-02T13:18:45.886Z ERROR sqlpage::webserver::database::sql_to_json] Failed to decode alloc::string::String value: invalid utf-8 sequence of 1 bytes from index 6
[2024-12-02T13:18:45.886Z ERROR sqlpage::webserver::database::sql_to_json] Failed to decode alloc::string::String value: invalid utf-8 sequence of 1 bytes from index 6
[2024-12-02T13:18:45.906Z INFO  actix_web::middleware::logger] 127.0.0.1 "GET /bug.sql HTTP/1.1" 200 2142 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36" 0.027387

You seem to have tested it on v0.32.0, if that all works, I will just wait till that version is available.

[lovasoa]

Indeed, I can reproduce the issue in v0.31

but not in 0.32

I'm going to mark this issue as fixed.

You can download pre-release versions if you need them, the easiest is to use the lovasoa/sqlpage:main image on docker.

[guspoweraa]

I am seeing this on mac using v0.35.2:

[2025-07-07T08:54:52.244Z ERROR sqlpage::webserver::database::sql_to_json] Failed to decode alloc::string::String value: invalid utf-8 sequence of 1 bytes from index 6
[2025-07-07T08:54:52.244Z ERROR sqlpage::webserver::database::sql_to_json] Failed to decode alloc::string::String value: invalid utf-8 sequence of 1 bytes from index 5
[2025-07-07T08:54:52.244Z ERROR sqlpage::webserver::database::sql_to_json] Failed to decode alloc::string::String value: invalid utf-8 sequence of 2 bytes from index 5
[2025-07-07T08:54:52.244Z ERROR sqlpage::webserver::database::sql_to_json] Failed to decode alloc::string::String value: invalid utf-8 sequence of 1 bytes from index 5

[lovasoa]

Can you share a reproduction of the issue ? Does a simple

select '00:00:00'::interval

trigger the issue ?

[guspoweraa]

OK so this is really wild:

select 'table' as component;

select '00:00:00'::interval;
select '1 day 2 hours'::interval;
select age(CURRENT_TIMESTAMP, '2024-07-11T00:09:00+00:00'::timestamp);
select age(CURRENT_TIMESTAMP, '2024-10-25 00:09:12.000000'::timestamp);

works perfectly.

SELECT
    age(CURRENT_TIMESTAMP, created) AS age
FROM
    issue
WHERE
    assignee = 'Gus.Power'

also works perfectly.

SELECT
    age(CURRENT_TIMESTAMP, created) AS age
FROM
    issue
WHERE
    assignee = $person

breaks!

[2025-07-07T10:06:10.219Z ERROR sqlpage::webserver::database::sql_to_json] Failed to decode alloc::string::String value: invalid utf-8 sequence of 1 bytes from index 5

[lovasoa]

Ok, can you make a minimal version that will be easy for me to reproduce? Maybe adding a create temp table if not exists and a set person = on top?

[guspoweraa]

CREATE TEMP TABLE IF NOT EXISTS issue AS
SELECT '1' AS issue_id,
       '2024-07-11 00:09:00.000000'::timestamp AS created;

SELECT 'table' AS component;

-- WORKS
SELECT * FROM issue;

-- WORKS
SELECT
    issue_id AS id,
    age(CURRENT_TIMESTAMP, created) AS age
FROM
    issue;

-- BROKEN - call url using ?issue_id=1
SELECT
    issue_id AS id,
    age(CURRENT_TIMESTAMP, created) AS age
FROM
    issue
WHERE
    issue_id = $id;

[lovasoa]

Ok, I can reproduce it. Let me reopen this issue !

Postgres returns textual interval representations for static queries, and binary values for parametrized queries.

Until we fix this, you will have to manually convert values to strings before returning them to sqlpage:

SELECT
    issue_id AS id,
    age(CURRENT_TIMESTAMP, created)::text AS age
FROM
    issue
WHERE
    issue_id = $id;

[guspoweraa]

Oh wow nicely done!

Postgres returns textual interval representations for static queries, and binary values for parametrized queries.

That is pretty wild!

Easy workaround - thank you @lovasoa !