How to calculate the time difference between start time column and end time column?

Question

I have 2 columns with start and end time values (yyyy-mm-dd ' ' hh-mm-ss 'UTC')

for example: start time (2023-12-24 01:11:19 UTC) end time (2023-12-24 02:12:47 UTC)

What I want to know is how do I create a new column showing the time difference between the start and end time? Would I need to remove the timezone?

I've come across DATEDIFF but every example I've seen using it have put in specific values instead of using a function to cover the whole table of values.

Answer 1

In T-SQL you need to remove the 'UTC' part using the LEFT function (this works since your date is in fixed length format, then you do a DateDiff for example:

select DATEDIFF(mi,LEFT('2023-12-24 01:11:19 UTC',19), LEFT('2023-12-24 02:12:47 UTC',19))

you can substitute the string literals in above example with the column names that holds the date values

Answer 2

You can use below (BigQuery)

timestamp_diff(end_time, start_time, minute) 

See below example

with your_table as (
  select '2023-12-24 01:11:19 UTC' as start_time, '2023-12-24 02:12:47 UTC' as end_time
)
select start_time, end_time, 
  timestamp_diff(timestamp(end_time), timestamp(start_time), minute) as diff_minutes
from your_table    

with output