Date & Time Arithmetic

Use this guide to write date & time arithmetic functions. For example,

To add or subtract seconds, minutes, hours, days, months or years to a given 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 hour, e.g.

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


To change the time of datetime value, use the @(datetime_add) function, 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:

@(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(date1, date2, unit):

Valid durations are “Y” for years, “M” for months, “W” for weeks, “D” for days, “h” for hour, “m” for minutes, “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 see a more complete list of functions here.

Updated on: 30/11/2021