Date & Time Arithmetic

Use this guide to write date and time arithmetic functions.

To add or subtract seconds, minutes, hours, days, months or years to or from a date, use @datetime_add:

@(datetime_add("2017-01-15", 5, "D"))    adds 5 days
@(datetime_add(fields.dob, -3, "M"))     subtracts 3 months

To create a datetime from a known datetime and a separate time, use @replace_time with @time, e.g.

Your next appointment is at @(replace_time(contact.created_on, time("15:00")))

To move a datetime value forward or back by a number of hours, use @datetime_add, e.g.

15 hours from now is @(datetime_add(contact.created_on, 15, "h"))

To replace the time component of any datetime, use @replace_time. For example, @(replace_time(now(), "10:30")) returns today at 10:30am.

To return the duration between date1 and date2 in the unit specified, use @datetime_diff.

@datetime_diff(date1, date2, unit)

Valid units are "Y" for years, "M" for months, "W" for weeks, "D" for days, "h" for hours, "m" for minutes and "s" for seconds.

@(datetime_diff("2017-01-15", "2017-01-17", "D")) → 2
@(datetime_diff("2017-01-15", "2017-05-15", "W")) → 17
@(datetime_diff("2017-01-15", "2017-05-15", "M")) → 4
@(datetime_diff("2017-01-17 10:50", "2017-01-17 12:30", "h")) → 1
@(datetime_diff("2017-01-17", "2015-12-17", "Y")) → -2

You can find a more complete list of functions in the expressions reference.