CODEWITHSUNDEEP
google-bigquerytimestampdifference
AustinR
AustinR

Reputation: 1

BigQuery how to create a column of the difference between two TIMESTAMP columns?

I have two columns in my table already in the TIMESTAMP format that show the beginning and end of bike ride data (these are called started_at and ended_at). I am attempting to create another column that shows the difference between each ride as the ride length.

I have managed to add a ride_length column, but cannot come up with code to find each difference to then input it into that column. I have been testing many options such as this:

SELECT TIMESTAMP_DIFF(ended_at, started_at, second) AS length_test FROM cohesive-pad-345117.capstone_bike_data.trip_data

The problem is the output is almost always wrong on all of the calculations. Sometimes the first row is correct, but the rest aren't. I'm not too knowledgeable on SQL queries yet so help would be appreciated.

Upvotes: 0

Views: 788

Answers (1)

Pratik Patil
Pratik Patil

Reputation: 832

If you already have a table that has 2 columns of start and end times, then it's actually pretty straightforward as you already mentioned in time diff function.

Following is just sample to show you:

with source_table as (

select timestamp_sub(current_timestamp(), INTERVAL cast(RAND()*200 as int64) DAY) as start_timestamp, timestamp_sub(current_timestamp(), INTERVAL cast(RAND()*10 as int64) DAY) as end_timestamp
union all 
select timestamp_sub(current_timestamp(), INTERVAL  cast(RAND()*200 as int64) DAY) as start_timestamp, timestamp_sub(current_timestamp(), INTERVAL  cast(RAND()*10 as int64) DAY) as end_timestamp
union all 
select timestamp_sub(current_timestamp(), INTERVAL  cast(RAND()*200 as int64) DAY) as start_timestamp, timestamp_sub(current_timestamp(), INTERVAL  cast(RAND()*10 as int64) DAY) as end_timestamp

)

select start_timestamp, end_timestamp, timestamp_diff(end_timestamp,start_timestamp, SECOND) as time_difference_in_seconds from source_table

Sample output looks like this: enter image description here

If you want this 3rd value to be added as a new column to the source table, then just overwrite "all" of your source table with addition of "timestamp_diff(end_timestamp,start_timestamp, SECOND) as time_difference_in_seconds" as another column.

Upvotes: 1

Related Questions