CODEWITHSUNDEEP
google-bigquery
Oscar Muñoz
Oscar Muñoz

Reputation: 439

Sum time to timestamp in bigquery

If have a table with a timestamp column and a time column like:

+---------------------+----------+
|    timestamp_col    | time_col |
+---------------------+----------+
| 2020-07-03 00:00:00 | 08:34:34 |
+---------------------+----------+

And I want to add them to get a timestamp column as "2020-07-03 08:34:34". i've tried "timestamp_add" but I have to split the time column into hours, minutes and secs, and add them one by one like this:

    timestamp_add(
      timestamp_add(
        timestamp_add(timestamp_col
          ,INTERVAL CAST(FORMAT_TIME("%H", time_col) as INT64) HOUR)
          ,INTERVAL CAST(FORMAT_TIME("%M", time_col) as INT64) MINUTE)
          ,INTERVAL CAST(FORMAT_TIME("%S", time_col) as INT64) SECOND)
    as timestamp_sum,

is there a more straight forward way?.

Upvotes: 0

Views: 1459

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

Below is for BigQuery Standard SQL

Assuming that time part of timestamp_col is always '00:00:00' as it is in your example - you can simply do below

TIMESTAMP(DATETIME(DATE(timestamp_col), time_col))

In case if above assumption about '00:00:00' is not the case - use below

TIMESTAMP_ADD(timestamp_col, INTERVAL UNIX_SECONDS(TIMESTAMP(DATETIME(DATE(1970, 1, 1), time_col))) SECOND)

Upvotes: 3

Related Questions