How in procedure correctly to compare datetimes? (PostgreSQL)

Question

In PostgreSQL database I create procedure which looks like this:

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;

When I try to start this procedure it raise error.

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

Error:

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

Where I make mistake?

"Where I make mistake?" -- Using varchar for a date/time. Use a date/time type like timestamp for the parameter custom_time. — sticky bit
– sticky bit, Commented Mar 29, 2019 at 12:09
I also tried it. I change it type of argument to timestamp. Result is the same when I call procedure. Do you have any ideas? — Nurzhan Nogerbek
– Nurzhan Nogerbek, Commented Mar 29, 2019 at 12:19

Saranraj Gunabalan · Accepted Answer · 2019-03-29 13:01:08Z

1

Try this..

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;

answered Mar 29, 2019 at 13:01

Saranraj Gunabalan

734 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nurzhan Nogerbek Over a year ago

Hello! Thank you! I used this part from your code CUSTOM_TIME timestamp without time zone. I think it's better to use procedure instead of function in my case cause I return nothing.

Collectives™ on Stack Overflow

How in procedure correctly to compare datetimes? (PostgreSQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related