时间戳

首页 > 解决方案 > 如何在程序中正确比较日期时间？(PostgreSQL)

sql - 如何在程序中正确比较日期时间？(PostgreSQL)

问题描述

在PostgreSQL数据库中，我创建了如下所示的过程：

CREATE OR REPLACE PROCEDURE tracker(CUSTOM_TIME VARCHAR) AS $FUNCTION$
    BEGIN
        UPDATE SURVEYS SET CONDITION = 3 WHERE CONDITION = 2 AND CUSTOM_TIME > END_PERIOD;
        UPDATE SURVEYS SET BLOCKED = TRUE WHERE CONDITION = 2 AND CUSTOM_TIME BETWEEN START_PERIOD AND END_PERIOD;
    END;
$FUNCTION$ LANGUAGE plpgsql;

当我尝试启动此过程时，它会引发错误。

CALL tracker('2019-03-29 16:37:00');

错误：

SQL Error [42883]: ERROR: operator does not exist: character varying > timestamp without time zone
No operator matches the given name and argument types. You might need to add explicit type casts.
PL/pgSQL function tracker(character varying) line 3 at SQL statement

我在哪里犯错？

标签： sqlpostgresql

解决方案

尝试这个..

CREATE OR REPLACE function tracker(CUSTOM_TIME timestamp without time zone) RETURNS void
AS $FUNCTION$
    BEGIN
        UPDATE SURVEYS SET CONDITION = 3 WHERE CONDITION = 2 AND CUSTOM_TIME > END_PERIOD;
        UPDATE SURVEYS SET BLOCKED = TRUE WHERE CONDITION = 2 AND CUSTOM_TIME BETWEEN START_PERIOD AND END_PERIOD;
    END;
$FUNCTION$
LANGUAGE plpgsql;

推荐阅读