Skip to content
Advertisement

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

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

enter image description here

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

enter image description here

Advertisement