Get difference in minutes between times with timezone

Question

I have a table in PostgreSQL:

CREATE TABLE tableA
(
    time_A time with timezone
);

How to get the difference in minutes between now() and time_A?

now() yields a timestamp, not a time, you need to be more specific how to deal with times before / after the stored time ... — Erwin Brandstetter, Apr 25 '14 at 18:32
I want get diff between current time with current time zone and another time with timezone in table — user3532941, Apr 25 '14 at 18:34

Erwin Brandstetter · Accepted Answer · 2022-05-09T00:16:32.533

The type timetz is broken by the design and its use is discouraged:

To factor in the time zone, use timestamptz instead. But be aware that the actual time zone is not stored explicitly:

Details for timestamp / time zone handling in Postgres:

Assuming time instead.

SELECT EXTRACT('epoch' FROM now()::time - time_a) / 60 AS minutes
FROM   tablea;

now() returns timestamptz, which is adjusted for the current time zone automatically. You get the local time when casting to time. See:

Subtracting two times yields an interval (which can be negative, of course).

Apply EXTRACT('epoch' FROM ...) to get the number of seconds in that interval. Divide by 60. Integer division truncates fractions.

1 Answers1