CODEWITHSUNDEEP
postgresql
OneCart
OneCart

Reputation: 705

Add minutes to CURRENT_TIMESTAMP in PostgreSQL

I have a procedure in PostgreSQL that I want to add the number of minutes to CURRENT_TIMESTAMP like below

timestamp_var := CURRENT_TIMESTAMP + interval '20 minutes';

But the number of minutes is a parameter.

Do we have the functions to do this?

Pls help me in this case

CREATE OR REPLACE FUNCTION modify_time(id users.id%TYPE, min integer) AS $$ 
BEGIN
UPDATE
        users
    SET
        modified_at = CURRENT_TIMESTAMP
    WHERE
        user_id = id;
END
$$ LANGUAGE plpgsql;

I want to add min minutes to CURRENT_TIMESTAMP thanks

Upvotes: 68

Views: 110753

Answers (3)

Tomasz
Tomasz

Reputation: 1416

If You need half minute or secounds :

SELECT current_timestamp + (50 * interval '1 seconds');

Upvotes: 2

stvsmth
stvsmth

Reputation: 3898

You can multiply intervals by integers. The following gives you a timestamp 20 minutes in the future:

select current_timestamp + (20 * interval '1 minute')

Or, as murison mentions in another answer to this question, there is a more succinct way to express this:

select current_timestamp + (20 ||' minutes')::interval

So, your code could look like:

CREATE OR REPLACE FUNCTION modify_time(id users.id%TYPE, min integer) AS $$ 
BEGIN
UPDATE
        users
    SET
        modified_at = CURRENT_TIMESTAMP + (min * interval '1 minute')
    WHERE
        user_id = id;
END
$$ LANGUAGE plpgsql;

Upvotes: 148

murison
murison

Reputation: 3985

the other way is

select current_timestamp + (20 ||' minutes')::interval

Upvotes: 48

Related Questions