How do I add number of days from original dates in new column

Question

Hopefully a quick one on BigQuery

I've tried intervals and days but can't quite seem to get what I want. For date row on the example table below I want and adjacent row in a new column that just adds 42 days to the original date and time (time is always 00:00:00 if that helps).

Desired output below:

original_datetime	date_time_plus_42_days
2016-04-01T00:00:00	plus 42 days to left column
2016-05-04T00:00:00	plus 42 days to left column
2018-05-17T00:00:00	plus 42 days to left column
2019-09-01T00:00:00	plus 42 days to left column
2016-04-01T00:00:00	plus 42 days to left column

Answer 1

Consider also below approach with explicit use of interval data type

select original_datetime,
  original_datetime + interval 42 day as date_time_plus_42_days
from your_table          

if applied to sample data in your question

with your_table as (
  select datetime '2016-04-01T00:00:00' original_datetime union all
  select '2016-05-04T00:00:00' union all
  select '2018-05-17T00:00:00' union all
  select '2019-09-01T00:00:00' union all
  select '2016-04-01T00:00:00' 
)                 

output is

Benefit of using interval data type is that in one shot you can add multiple units - for example not just days but also hours as in example below

select original_datetime,
  original_datetime + make_interval(day => 42, hour => 5) as date_time_plus_42_days
from your_table

with output

Answer 2

The function you are looking for is called: DATETIME_ADD. It is documented here.

For instance:

WITH table AS (
     SELECT DATETIME("2016-04-01T00:00:00") AS datetime)
SELECT
  datetime,
  DATETIME_ADD(datetime, INTERVAL 42 DAY) as datetime_plus_42
FROM table;