CODEWITHSUNDEEP
google-bigquery
Javier Muñoz
Javier Muñoz

Reputation: 780

How to SUBTRACT TIMESTAMP in BigQuery

I need to subtract the followings TIMESTMAP in BigQuery, and get the following result:

02/01/2020 20:30:20 - 02/01/2020 20:26:07 = 00:04:13

The output format must be in HH:MM:SS

I've tried to use the TIMESTAMP_DIFF function but without success.

Do you have any suggetions how to do it?

Upvotes: 0

Views: 1108

Answers (2)

Pentium10
Pentium10

Reputation: 207828

A way to do this:

SELECT 
    time(
       TIMESTAMP_SECONDS
          (timestamp_diff(
            PARSE_TIMESTAMP('%m/%d/%Y %T','02/01/2020 20:30:20'),
            PARSE_TIMESTAMP('%m/%d/%Y %T','02/01/2020 20:26:07')
           ,SECOND)
          ))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think this will do what you want:

select time_add(time(0, 0, 0),
                interval timestamp_diff(ts1, ts2, second) second
               )

Upvotes: 1

Related Questions