如何在plpgsql中编写比较日期与没有时区时间戳的函数？

Question

我想要编写一个函数，它返回一个包含firstDate和lastDate之间所有行的表。行具有没有时区的数据类型时间戳，它们还必须具有特定的节点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; 

列measurement_timestamp是没有时区的时间戳类型，格式为yy dd hh-mm-ss。

当我运行SELECT * FROM get_measurements_by_node_and_date(1, '2020-5-1', '2020-5-24')时

我得到以下错误：

错误:运算符不存在:时间戳没有时区<=整数行10：.measurements_lora.measurement_timestamp <= 2020-05.

我不明白为什么它说“整数”，因为我明确地将firstDate和lastDate定义为date类型。

Answer 1

What Pavel said.

更重要的是，没有任何迹象表明PL/pgSQL需要一开始。一个普通的(准备好的) SELECT语句可以做到这一点。或者SQL函数，如果您想将它保存在数据库中的话。请参见：

• Difference between language sql and language plpgsql in PostgreSQL functions

并涉及：

与firstDate和lastDate之间的所有行

精确定义包含/排它的上/下界，以避免意外的角情况结果。当将timestamp列与date进行比较时，后者被强制使用时间戳，该时间戳表示一天中的第一个实例：YYYY.MM.DD 00:00:00。

您的查询说：

measurement_timestamp <= lastDate AND measurement_timestamp >= 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$;

1这包括所有的lastDate，并有效地。你只要add / subtract an integer value to / from a date to add / subtract days就行了。显式强制转换到::timestamp是可选的，因为日期将自动在表达式中强制执行。但既然我们在努力消除混乱..。

相关信息：

• How do I determine the last day of the previous month using PostgreSQL?

除1外：

列measurement_timestamp为timestamp without time zone类型，格式为yy dd hh-mm-ss。

不是的。timestamp值没有格式化，句号。它们只是时间戳值(内部存储为自时代以来的微秒数)。显示完全独立于值，可以在不改变值的情况下以101种方式进行调整。摆脱这种误解，更好地理解正在发生的事情。请参见：

• Ignoring time zones altogether in Rails and PostgreSQL

旁白2：

关于SQL BETWEEN的狡诈性

• How to add a day/night indicator to a timestamp column?

旁白3：

请考虑Postgres中的合法、小写标识符。first_date而不是firstDate。请参见：

• Are PostgreSQL column names case-sensitive?

相关信息：

• PostgreSQL function returning a data cube
• Postgresql trying to use execute format in a function but getting column not found error when giving string format in coalesce

Answer 2

在这种情况下，首先编写执行的查询是很好的。我试着减少你的例子：

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

我也收到了同样的错误信息。所以有不止一个错误：

1. 动态查询无效- where子句如下

where current_timestamp <= 2020-05-25

你可以看到，这是无效的-没有引号。当您手动使用引号时，您可以修复它(但这是一个强错误，不要这样做，或者可以使用函数quote_literal，比如where current_timestamp <= ' || quote_literal(d) )。

现在生成的查询是正确的：

从generate_series(1,100)中选择1，在本例中使用current_timestamp <= <=，使用EXECUTE USING要好得多。当变量用作查询参数(而不是表或列名)时，可以使用USING子句。那么您就不需要使用引用：

创建或替换函数public.foo(d date)返回表(o整型)语言plpgsql作为$function$声明Q文本；从current_timestamp <= $1‘的generate_series(1,100)开始Q := 'select 1；返回使用d；end的查询执行Q；当不需要时使用动态SQL (如您的示例)。使用RETURN QUERY EXECUTE语句没有任何明显的原因。您可以只使用RETURN QUERY：

创建或替换函数get_measurements_by_node_and_date(nodeID整数、firstDate日期、lastDate日期)返回表(measurement_id整数、node_id整数、carbon_dioxide双重精度、碳氢化合物双精度、温度双精度、湿度双精度、air_pressure双精度、measurement_timestamp时间戳无时区)作为$$开始返回查询的选择measurements_lora.id，measurements_lora.node_id，measurements_lora.carbon_dioxide，measurements_lora.hydrocarbons，measurements_lora.temperature，measurements_lora.humidity，measurements_lora.air_pressuremeasurements_lora.measurement_timestamp作为public.measurements_lora的测度，其中measurements_lora.measurement_timestamp <= lastDate和measurements_lora.measurement_timestamp >= firstDate和measurements_lora.node_id = nodeID；END $$语言plpgsql;