如何在plpgsql中编写一个函数来比较日期和没有时区的时间戳?

xhv8bpkk  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(447)

我想写一个函数,返回一个表,表中的所有行在 firstDate 以及 lastDate . 行具有不带时区的数据类型timestamp它们还必须具有特定的节点id。
这是我的职责:

CREATE OR REPLACE FUNCTION get_measurements_by_node_and_date(nodeID INTEGER, firstDate date, lastDate date) 
RETURNS TABLE (measurement_id INTEGER, node_id INTEGER, carbon_dioxide DOUBLE PRECISION, 
                    hydrocarbons DOUBLE PRECISION, temperature DOUBLE PRECISION, 
                    humidity DOUBLE PRECISION, 
                    air_pressure DOUBLE PRECISION, 
                    measurement_timestamp timestamp without time zone ) AS
$$
    DECLARE
       sql_to_execute TEXT;
    BEGIN
        SELECT 'SELECT measurements_lora.id, 
                       measurements_lora.node_id,
                       measurements_lora.carbon_dioxide,
                       measurements_lora.hydrocarbons,
                       measurements_lora.temperature,
                       measurements_lora.humidity,
                       measurements_lora.air_pressure,
                       measurements_lora.measurement_timestamp AS  measure
                  FROM public.measurements_lora 
                  WHERE  measurements_lora.measurement_timestamp <= '||lastDate||'
                  AND    measurements_lora.measurement_timestamp >= '||firstDate||'           
                  AND    measurements_lora.node_id = '||nodeID||' ' 
          INTO sql_to_execute;
        RETURN QUERY EXECUTE sql_to_execute;
    END
$$ LANGUAGE plpgsql;

列测量时间戳是不带时区的时间戳类型,格式为yy-mm-dd hh-mm-ss
当我跑的时候 SELECT * FROM get_measurements_by_node_and_date(1, '2020-5-1', '2020-5-24') 我得到以下错误:

ERROR:  operator does not exist: timestamp without time zone <= integer
LINE 10: ...   WHERE  measurements_lora.measurement_timestamp <= 2020-05...

我不明白为什么它说“整数”,因为我定义得很清楚 firstDate 以及 lastDate 作为类型 date .

oknwwptz

oknwwptz1#

帕维尔说的。
此外,没有任何迹象表明需要从pl/pgsql开始。平原 SELECT 我能做到。或者一个sql函数,如果您想将其持久化到数据库中。请参见:
postgresql函数中sql语言与plpgsql语言的区别
关于:
所有的行间 firstDate 以及 lastDate 精确定义包含/排除的上限/下限,以避免意外的角大小写结果。在比较 timestamp 列到a date ,后者被强制为表示当天第一个示例的时间戳: YYYY.MM.DD 00:00:00 .
你的问题是:

measurement_timestamp <= lastDate AND measurement_timestamp >= firstDate

... 包括所有 firstDate ,但排除所有 lastDate 除了00:00的第一个(公共)示例。通常不是你想要的。根据你的公式,我想这就是你真正想要的:

CREATE OR REPLACE FUNCTION get_measurements_by_node_and_date(node_id integer
                                                           , firstDate date
                                                           , lastDate date) 
  RETURNS TABLE (measurement_id integer
               , node_id integer
               , carbon_dioxide float8
               , hydrocarbons float8
               , temperature float8
               , humidity float8
               , air_pressure float8
               , measurement_timestamp timestamp)
  LANGUAGE sql STABLE AS
$func$
   SELECT m.id
        , m.node_id
        , m.carbon_dioxide
        , m.hydrocarbons
        , m.temperature
        , m.humidity
        , m.air_pressure
        , m.measurement_timestamp -- AS measure  -- just documentation
   FROM   public.measurements_lora m
   WHERE  m.node_id = _node_id
   AND    m.measurement_timestamp >= firstDate::timestamp
   AND    m.measurement_timestamp < (lastDate + 1)::timestamp  -- ①!
$func$;

① 这包括所有 lastDate ,而且效率很高。你只需要加/减一个 integer 值到/来自 date 加减天数。显式强制转换为 ::timestamp 是可选的,因为日期将在表达式中自动强制。但既然我们正在努力消除混乱。。。
相关:
如何使用postgresql确定上个月的最后一天?
旁白1:
measurement_timestamp 属于类型 timestamp without time zone 格式为yy-mm-dd hh-mm-ss
不。 timestamp 值未格式化,句点。它们只是时间戳值(内部存储为自epoch以来的微秒数)。显示完全独立于值,可以在不改变值的情况下以100多种方式进行调整。摆脱这种误解,以便更好地理解发生了什么。请参见:
在rails和postgresql中完全忽略时区
旁白2:
关于sql的狡猾本质 BETWEEN :
如何在时间戳列中添加日/夜指示器?
旁白3:
在postgres中考虑合法的小写标识符。 first_date 而不是 firstDate . 请参见:
postgresql列名是否区分大小写?
相关:
返回数据立方体的postgresql函数
postgresql试图在函数中使用execute format,但在coalesce中提供字符串格式时,get column not found出错

mrwjdhj3

mrwjdhj32#

在这种情况下,最好先编写已执行的查询。我尽量减少你的例子:

CREATE OR REPLACE FUNCTION public.foo(d date)
 RETURNS TABLE(o integer)
 LANGUAGE plpgsql
AS $function$
declare q text;
begin
  q := 'select 1 from generate_series(1,100) where current_timestamp <= ' || d ;
  raise notice '%', q;
  return query execute q;
end;
$function$

postgres=# select * from foo('2020-05-25');
NOTICE:  00000: select 1 from generate_series(1,100) where current_timestamp <= 2020-05-25
LOCATION:  exec_stmt_raise, pl_exec.c:3826
ERROR:  42883: operator does not exist: timestamp with time zone <= integer
LINE 1: ...om generate_series(1,100) where current_timestamp <= 2020-05...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  select 1 from generate_series(1,100) where current_timestamp <= 2020-05-25
CONTEXT:  PL/pgSQL function foo(date) line 6 at RETURN QUERY

我收到了同样的错误信息。所以有不止一个错误:
动态查询无效-where子句如下所示 where current_timestamp <= 2020-05-25 你可以看到,这是无效的-没有引号。当您手动使用引号时,您可以修复它(但这是一个很强的错误,不要这样做,或者您可以使用函数) quote_literal 就像 where current_timestamp <= ' || quote_literal(d) .
现在生成的查询是正确的:

select 1 from generate_series(1,100) where current_timestamp <= '2020-05-25'

但在这种情况下,使用 EXECUTE USING . 当变量用作查询参数(而不是表或列名)时,可以使用 USING 条款。那么你就不需要引用:

CREATE OR REPLACE FUNCTION public.foo(d date)
 RETURNS TABLE(o integer)
 LANGUAGE plpgsql
AS $function$
declare q text;
begin
  q := 'select 1 from generate_series(1,100) where current_timestamp <= $1';
  return query execute q using d;
end;
$function$

但最大的错误是在不必要时使用动态sql(如您的示例)。没有任何明显的理由说明你为什么使用 RETURN QUERY EXECUTE 声明。你可以只用 RETURN QUERY :

CREATE OR REPLACE FUNCTION get_measurements_by_node_and_date(nodeID INTEGER, firstDate date, lastDate date) 
RETURNS TABLE (measurement_id INTEGER, node_id INTEGER, carbon_dioxide DOUBLE PRECISION, 
               hydrocarbons DOUBLE PRECISION, temperature DOUBLE PRECISION, 
               humidity DOUBLE PRECISION, 
               air_pressure DOUBLE PRECISION, 
               measurement_timestamp timestamp without time zone ) AS
$$
BEGIN
   RETURN QUERY SELECT measurements_lora.id, 
                       measurements_lora.node_id,
                       measurements_lora.carbon_dioxide,
                       measurements_lora.hydrocarbons,
                       measurements_lora.temperature,
                       measurements_lora.humidity,
                       measurements_lora.air_pressure,
                       measurements_lora.measurement_timestamp AS  measure
                  FROM public.measurements_lora 
                  WHERE measurements_lora.measurement_timestamp <= lastDate 
                    AND measurements_lora.measurement_timestamp >= firstDate           
                    AND measurements_lora.node_id = nodeID;
END
$$ LANGUAGE plpgsql;

相关问题