CODEWITHSUNDEEP
postgresqltype-conversionpostgresql-9.2
Stephan
Stephan

Reputation: 43053

How to convert interval to timestamp with time zone with postgresql?

I want to perform this simple query :

SELECT
    pid,
    MIN(interval '5 minutes' - current_timestamp - state_change)
FROM
    pg_stat_activity
AND
    current_timestamp - state_change <= interval '5 minutes'
GROUP BY 
    pid
ORDER BY
    2 ASC
LIMIT 1;

But PG complains that there is no operator between interval and timestamp with time zone.

How can I convert my interval to timestamp ?

Upvotes: 0

Views: 6996

Answers (1)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61656

The impossible conversion is not the problem, it's a matter of evaluation order. Without changing the order of arguments you may write it with parentheses:

interval '5 minutes' - (current_timestamp - state_change)

and it would work since interval - interval is supported. Without the parentheses it doesn't work since interval - timestamp would be evaluated first and it's not implemented.

As a sidenote, since pid is unique in pg_stat_activity, the GROUP BY pid and MIN should be suppressed, as in:

SELECT
    pid,
    interval '5 minutes' - (current_timestamp - state_change)
FROM
    pg_stat_activity
WHERE
    current_timestamp - state_change <= interval '5 minutes'

Upvotes: 4

Related Questions