Calculate time difference over the first and the last table row-CodePudding

Within postgresql, I'm trying to write a query that calculates the time difference between a time stamp of the first row and a time stamp of the last row:

(select public."ImportLogs"."DateTimeStamp" as migration_start from public."ImportLogs" order by public."ImportLogs"."DateTimeStamp" asc limit 1)
union
(select public."ImportLogs"."DateTimeStamp" as migration_end from public."ImportLogs" order by public."ImportLogs"."DateTimeStamp" desc limit 1);

I tried to get the time difference between migration_start and migration_end, but I couldn't get it to work. How can I achieve this?

CodePudding user response：

We can substract min(DateTimeStamp) from the max(DateTimeStamp)` and cast the difference as time.

select 
  cast(
     max(DateTimeStamp) 
   - min(DateTimeStamp)
     as time) TimeDiffernce 
from ImportLogs

| timediffernce |
| :------------ |
| 00:00:10      |

db<>fiddle here