CODEWITHSUNDEEP
sqltimegoogle-bigquerytimestamp
mosoho
mosoho

Reputation: 23

BigQuery - Convert TIMESTAMP to HH:MM:SS and then identify TIME_DIFF

in BigQuery I would like to

  1. Convert a TIMESTAMP (YYYY-MM-DD HH:MM:SS) format to TIME (HH:MM:SS)
  2. Calculate TIME_DIFF between 2 newly converted TIME points to get the duration

Where I am right now

SELECT
   started_at,
   ended_at,
   (ended_at - started_at) AS trip_duration,
   TIMESTAMP_DIFF(ended_at, started_at, SECOND) AS trip_duration_seconds
started_at ended_at trip_duration trip_duration_seconds
2023-02-12 16:48:18 UTC 2023-02-12 16:55:10 UTC 0-0 0 0:6:52 412

Desired result something like this

started_at ended_at trip_duration
16:48:18 16:55:10 00:06:52

I could probably now chop it up as STRING, and then back to a TIME format. But there must be a very easy solution!

Upvotes: 2

Views: 1827

Answers (1)

Jaytiger
Jaytiger

Reputation: 12254

You can consider below.

WITH sample_table AS (
  SELECT TIMESTAMP '2023-02-12 16:48:18 UTC' started_at, 
         TIMESTAMP '2023-02-12 16:55:10 UTC' ended_at
)
SELECT EXTRACT(TIME FROM started_at) AS started_at,
       EXTRACT(TIME FROM ended_at) AS ended_at,
       TIME_ADD(
         TIME '0:0:0',
         INTERVAL TIMESTAMP_DIFF(ended_at, started_at, SECOND) SECOND
       ) AS trip_duration 
  FROM sample_table

-- query result
+------------+----------+---------------+
| started_at | ended_at | trip_duration |
+------------+----------+---------------+
| 16:48:18   | 16:55:10 | 00:06:52      |
+------------+----------+---------------+

Upvotes: 3

Related Questions