Increment Date

Shift dates forward or backward by an amount you set.

What it does

Increment Date moves every date in a column. You give an amount and a unit. The result is still a date. Use it for due dates, renewal dates and reminder dates.

Shift a date column

  1. Type increment in the Search transforms box, in the Pipeline panel.
  2. Select Increment Date in the results list.
  3. Under Select column, pick your date column. Only date columns are listed.
  4. Type a number in Amount.
  5. Open Unit and select one of day, week, month, year, hour or minute.
  6. Type a name in New Column Name.
  7. Click Apply.

Example: a subscription table has a renewal_date column. Set Amount to 30 and Unit to day. Name the result grace_period_end.

Go backward in time

Type a minus sign in front of the amount. An Amount of -7 with a Unit of day moves each date one week earlier. Use this to build a reminder date before a renewal.

Shift only some rows

The panel has an Add Condition button. Use it to shift the rows you choose and leave the others alone.

  1. Click Add Condition. A window opens.
  2. Pick a column, a comparison and a value.
  3. Click Apply Condition.
  4. Click Apply.

Example: shift renewal_date by one month only where plan is Annual. Rows on other plans are not touched.

Be careful when you combine a condition with a new column. Rows that do not match get an empty value in that new column. To keep the old date on those rows, write the result back into the source column.

Where the new date goes

The Apply results into section starts on New Column (Date). Type a name to add a column and keep the original dates. Select Existing Column to write over a column you already have.

Leave the name box empty and the source column is updated in place. The old dates are then gone from the grid.

For SQL users

The shift becomes an interval. Adding 30 days into a new column runs as:

SELECT *, "renewal_date" + INTERVAL '30 day' AS "grace_period_end" FROM data
Try Increment Date with sample data →

Related Operations