1

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?

Randy Wink
  • 71
  • 3

1 Answers1

2

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