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 |
Advertisement
Answer
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