Datetime difference

I have a date column “start_date” with type TIMESTAMPTZ. i want to calculate time difference in minutes with current time. i can find diff with following query

select now() - start_date from mytable

but how to convert this in minutes ?


Hi @muhammad.habib,

The only way I can think of doing this is by using extract() and manipulating that data.

Use this snippet for an example select extract('minute',now());

If you wish to pass in a literal to the second argument, be sure to cast your data to a timestamp. (select extract('minute',cast ('2019-09-03 15:56:21.679092+00:00' as timestamptz));)


i guess select now() - start_date from mytable return intervals not timestamptz. i need datetime difference in minutes or a way i can extract min and hours from intervals. i have tried

select extract(minute from ‘00:19:05.102152’); its working

but when i try

select extract(‘hour’ from now() - updated_at), extract(‘minute’ from now() - updated_at )
FROM “mytable”

its throwing exception .

I think i found a way for you to determine the difference in minutes.

root@:26257/test> select extract_duration('minute', '01:19:05.102152');
(1 row)

What exception are you getting?

i got difference in min with following query

SELECT cast(cast((now() - updated_at) as int)/60 as int) AS expiry_min
FROM “table”


Glad to hear you were able to figure it out.