java - 如何将值从我的 REST 端点传递到我的 PostgreSQL time_bucket_gapfill() 函数

Question

我正在尝试通过 REST 端点使用 TimescaleDB 从我的 PostgreSQL 数据库中调用 time_bucket_gapfill() 函数。显然，我使用该函数将我的数据聚合到选定间隔的桶中。如果用户请求一周的数据，我每天汇总数据，如果他们请求一天的数据，我每小时汇总数据，等等。

我正在使用 time_bucket_gapfill() 函数，如下所示：

time_bucket_gapfill('1 day', timestamp) <- This gives me data on daily basis
time_bucket_gapfill('1 hour', timestamp) <- This gives me data on hourly basis

但是，函数中的第一个参数在查询中没有硬编码，它由 REST 端点提供给查询，并作为数据类型字符串提供。这就是导致问题的原因，因为它给了我以下错误：

ERROR: function public.time_bucket_gapfill(character varying, timestamp without time zone) does not exist

如何以正确的格式注入值？

标签： javapostgresqlspring-bootfunctiontimescaledb

Answer 1

\df您可以使用以下命令检查可用函数的签名：

 \df public.time_bucket_gapfill;
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | time_bucket_gapfill
Result data type    | bigint
Argument data types | bucket_width bigint, ts bigint, start bigint DEFAULT NULL::bigint, finish bigint DEFAULT NULL::bigint
Type                | func
-[ RECORD 2 ]-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | time_bucket_gapfill
Result data type    | integer
Argument data types | bucket_width integer, ts integer, start integer DEFAULT NULL::integer, finish integer DEFAULT NULL::integer
Type                | func
-[ RECORD 3 ]-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | time_bucket_gapfill
Result data type    | date
Argument data types | bucket_width interval, ts date, start date DEFAULT NULL::date, finish date DEFAULT NULL::date
Type                | func
-[ RECORD 4 ]-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | time_bucket_gapfill
Result data type    | timestamp with time zone
Argument data types | bucket_width interval, ts timestamp with time zone, start timestamp with time zone DEFAULT NULL::timestamp with time zone, finish timestamp with time zone DEFAULT NULL::timestamp with time zone
Type                | func
-[ RECORD 5 ]-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | time_bucket_gapfill
Result data type    | timestamp without time zone
Argument data types | bucket_width interval, ts timestamp without time zone, start timestamp without time zone DEFAULT NULL::timestamp without time zone, finish timestamp without time zone DEFAULT NULL::timestamp without time zone
Type                | func
-[ RECORD 6 ]-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | time_bucket_gapfill
Result data type    | smallint
Argument data types | bucket_width smallint, ts smallint, start smallint DEFAULT NULL::smallint, finish smallint DEFAULT NULL::smallint
Type                | func

如您所见character varying，您需要将其转换为间隔。

select '1 month'::interval;
-[ RECORD 1 ]---
interval | 1 mon