CODEWITHSUNDEEP
google-bigquery
rbkk2016
rbkk2016

Reputation: 215

Add five hours to timestamp field

I want to add few hours eg: 5hours 30 mins to timestamp field using bigquery. My timestamp field is in the format - '2016-05-03 21:35:03'

How can i do this in bigquery?

Upvotes: 6

Views: 24947

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

SELECT 
  ts, 
  DATE_ADD(ts, 330, "MINUTE") AS ts_plus_530 
FROM 
  (SELECT CURRENT_TIMESTAMP() AS ts),
  (SELECT TIMESTAMP("2016-05-03 21:35:03") AS ts)

See DATE_ADD for more details

Upvotes: 5

Elliott Brossard
Elliott Brossard

Reputation: 33765

For completeness, the equivalent standard SQL query (uncheck "Use Legacy SQL" under "Show Options") would be:

WITH T AS (
  SELECT ts
  FROM UNNEST([CURRENT_TIMESTAMP(),
               TIMESTAMP("2016-05-03 21:35:03")]) AS ts)
SELECT TIMESTAMP_ADD(ts, INTERVAL 330 MINUTE) AS ts_plus_530
FROM T;
+---------------------+
|     ts_plus_530     |
+---------------------+
| 2016-08-09 04:18:05 |
| 2016-05-04 03:05:03 |
+---------------------+

Documentation for TIMESTAMP_ADD is here: https://cloud.google.com/bigquery/sql-reference/functions-and-operators#timestamp_add

Upvotes: 14

Related Questions