Reputation: 439
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
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