Date & Time Functions
Smart Flows comes with a large number of date & time functions, which can be used to do calculations with dates and times.
Concept
All of the date & time calculations are done using functions. Smart Flows has a long list of functions that you can use in expressions. Expressions are accessed via pseudo-fields or formulas.
It's good to read up on expressions (read more about expressions here) so that you have a basic understanding of how to use them properly. This will allow you to dive right in to the date & time functions.
Base date
Some functions require that you specify a base date. A base date is necessary for some functions that deal with months/years, as not every month/year has the same length. Changing the base date can then also change the output of those functions.
For instance, the function formatPeriod can be used to convert a time period into a number of months and days. Here's how the base date can change the output; in the following functions, the final parameter is the base date, and that's the only parameter that's changed:
formatPeriod(8264000000,'M \'months and \'d \'days\'','2016-01-01') = 3 months and 4 daysformatPeriod(8264000000,'M \'months and \'d \'days\'','2016-02-01') = 3 months and 5 daysformatPeriod(8264000000,'M \'months and \'d \'days\'','2016-03-01') = 3 months and 3 days
The difference here is due to the month of February being shorter. If you don't specify a base date, the current date is used.
Examples
While this page contains all the info you'll need on these functions, you may be wondering how to use them in your specific use case. Therefore, we've described a few examples.
Functions
Current
Use these functions to get the current date, time or datetime.
| Name | Input | Output |
|---|---|---|
| currentDate | none | The current date. |
| currentTime | none | The current time. |
| currentDateTime | none | The current date & time. |
The output of these functions is in the dateTime format as described on Date & time formats.
Add or subtract
Add days, months or years
Use the following functions to add a number of days/months/years to a date or datetime.
| Name & Arguments | Input | Output |
|---|---|---|
| addDays(datetime, amount) |
|
The provided dateTime with a number of days added. |
| addDaysToDate(date, amount) |
|
The provided date with a number of days added. |
| addMonths(datetime, amount) |
|
The provided dateTime with a number of months added. |
| addMonthsToDate(date, amount) |
|
The provided date with a number of months added. |
| addYears(datetime, amount) |
|
The provided dateTime with a number of years added. |
| addYearsToDate(date, amount) |
|
The provided date with a number of years added. |
The output of these functions is either in the dateTime or the date format. Both are described on Date & time formats.
Subtract days, months or years
Use the following functions to subtract a number of days/months/years from a date or datetime.
| Name & Arguments | Input | Output |
|---|---|---|
| subDays(datetime, amount) |
|
The provided dateTime with a number of days subtracted. |
| subDaysFromDate(date, amount) |
|
The provided date with a number of days subtracted. |
| subMonths(datetime, amount) |
|
The provided dateTime with a number of months subtracted. |
| subMonthsFromDate(date, amount) |
|
The provided date with a number of months subtracted. |
| subYears(datetime, amount) |
|
The provided dateTime with a number of years subtracted. |
| subYearsFromDate(date, amount) |
|
The provided date with a number of years subtracted. |
The output of these functions is either in the dateTime or the date format. Both are described on Date & time formats.
Add seconds, minutes or hours
Use the following functions to add a number of seconds/minutes/hours to a time or datetime.
| Name & Arguments | Input | Output |
|---|---|---|
| addSeconds(datetime, amount) |
|
The provided dateTime with a number of seconds added. |
| addSecondsToTime(time, amount) |
|
The provided time with a number of seconds added. |
| addMinutes(datetime, amount) |
|
The provided dateTime with a number of minutes added. |
| addMinutesToTime(time, amount) |
|
The provided time with a number of minutes added. |
| addHours(datetime, amount) |
|
The provided dateTime with a number of hours added. |
| addHoursToTime(time, amount) |
|
The provided time with a number of hours added. |
The output of these functions is either in the dateTime or the time format. Both are described on Date & time formats.
Subtract seconds, minutes or hours
Use the following functions to subtract a number of seconds/minutes/hours from a time or datetime.
| Name & Arguments | Input | Output |
|---|---|---|
| subSeconds(datetime, amount) |
|
The provided dateTime with a number of seconds subtracted. |
| subSecondsFromTime(time, amount) |
|
The provided time with a number of seconds subtracted. |
| subMinutes(datetime, amount) |
|
The provided dateTime with a number of minutes subtracted. |
| subMinutesFromTime(time, amount) |
|
The provided time with a number of minutes subtracted. |
| subHours(datetime, amount) |
|
The provided dateTime with a number of hours subtracted. |
| subHoursFromTime(time, amount) |
|
The provided time with a number of hours subtracted. |
The output of these functions is either in the dateTime or the time format. Both are described on Date & time formats.
Format according to a pattern
Use these functions to turn a date, time, datetime or period into a text string according to a given pattern.
This has the same effect as using the custom date option in the mapping window as described on this page:
| Name & Arguments | Input | Output |
|---|---|---|
| formatDate(date, pattern) |
|
The provided date, in the provided pattern. |
| formatDateR(date, pattern, locale) |
|
The provided date, in the provided pattern, according to the provided locale. |
| formatDateTime(date, pattern) |
|
The provided dateTime, in the provided pattern. |
| formatDateTimeR(date, pattern, locale) |
|
The provided dateTime, in the provided pattern, according to the provided locale. |
| formatTime(time, pattern) |
|
The provided time, in the provided pattern. |
| formatTimeR(time, pattern, locale) |
|
The provided time, in the provided pattern, according to the provided locale. |
| formatPeriod(period, pattern, base date) |
|
The provided period, in the provided pattern, starting from the base date. |
| formatPeriodIso(period, base date) |
|
The provided period, in the ISO8601 pattern (explained on Date & time formats), starting from the base date. |
Convert a value into
Use these functions to convert a text string into a date, time or datetime. This is pretty much the opposite of the "format" functions, where a date, time or datetime was converted into a string.
The primary use of these functions is to turn something that looks like a date or time into a real date or time. For instance: if you have a field with 2015-07-03 as value, you may be tempted to think that this is a date, but it is a text string. If you want to use that text string as a real date (for calculations or for further formatting), you'll need to convert it into a real date first.
| Name & Arguments | Input | Output |
|---|---|---|
| toDate(text string) |
|
The provided text string converted into a date, according to the rules on Date & time formats. |
| toDateF(text string, pattern) |
|
The provided text string converted into a date, according to the provided pattern. |
| toDateFR(text string, pattern, locale) |
|
The provided text string converted into a date, according to the provided pattern, using the provided locale. |
| toDateTime(text string) |
|
The provided text string converted into a datetime, according to the rules on Date & time formats. |
| toDateTimeF(text string, pattern) |
|
The provided text string converted into a datetime, according to the provided pattern. |
| toDateTimeFR(text string, pattern, locale) |
|
The provided text string converted into a datetime, according to the provided pattern, using the provided locale. |
| toTime(text string) |
|
The provided text string converted into a time, according to the rules on Date & time formats. |
| toTimeF(text string, pattern) |
|
The provided text string converted into a time, according to the provided pattern. |
| toTimeFR(text string, pattern, locale) |
|
The provided text string converted into a time, according to the provided pattern, using the provided locale. |
Number of ... in period
Use these functions to convert a period into a number representing the number of seconds, minutes, hours or days in the period.
Months and years are not supported due to their variable length.
| Name & Arguments | Input | Output |
|---|---|---|
| toSeconds(period) |
|
The number of seconds in the provided period. |
| toMinutes(period) |
|
The number of minutes in the provided period. |
| toHours(period) |
|
The number of hours in the provided period. |
| toDays(period) |
|
The number of days in the provided period. |
These functions return a decimal number, which may not always be practical. You can make use of the ceil and floor functions to respectively round up or down, like this:
toDays(5000000000) = 57.87037037floor(toDays(5000000000)) = 57ceil(toDays(5000000000)) = 58
First or last of month
Use these functions to find out the first or last day of the month.
| Name & Arguments | Input | Output |
|---|---|---|
| firstDateOfMonth(date) |
|
A dateTime on the first day of the month of the provided date. The time is left unchanged. |
| lastDateOfMonth(date) |
|
A dateTime on the last day of the month of the provided date. The time is left unchanged. |
| lastDayOfMonth(date) |
|
The number of the last day of the month. |
Create a period
Use these functions to create a period. This period can then be used in other functions that require a period as input.
| Name & Arguments | Input | Output |
|---|---|---|
| createPeriod(years, months, weeks, days, hours, minutes, seconds, milliseconds, base date) |
|
A period spanning the sum of all the provided time elements. |
| createPeriodDHM(days, hours, minutes, base date) |
|
A period spanning the sum of all the provided time elements. |
| createPeriodYMD(years, months, days, base date) |
|
A period spanning the sum of all the provided time elements. |
| createPeriodIso(value, base date) |
|
A period spanning the sum of all the time elements in the provided ISO8601 period. |
| periodBetween(date 1, date 2) |
|
A period spanning the time between the two provided dates/datetimes/times. |
Break a period down into parts
Use these functions to break a period down into its parts.
The output of these functions is the number of years, months, days, hours, minutes or seconds in a period, without going over the limit. This means that you can't ever have 15 months (11 max), 72 minutes (59 max) etc. So if you have a period of about 2 years, using the getPeriodMinutes function on its own makes no sense, as it will always return 59.
Therefore, using these functions only makes sense if you use them together. Here are a few typical use cases:
- You have been a member of this community for x years, x months and x days.
- There are x days and x hours left until your subscription will be cancelled automatically. (if the subscription can be longer than 1 month, you should add x months as well)
For cases such as these, these functions are very useful as you do not have to calculate the overflow (e.g. 14 months = 1 year + 2 months) yourself.
| Name & Arguments | Input | Output |
|---|---|---|
| getPeriodYears(period, base date) |
|
The number of years in the provided period, calculated from the base date. |
| getPeriodMonths(period, base date) |
|
The number of months (maximum 11) in the provided period, calculated from the base date. |
| getPeriodDays(period, base date) |
|
The number of days (maximum 30/29/28/27, depending on the base date) in the provided period, calculated from the base date. |
| getPeriodHours(period, base date) |
|
The number of hours (maximum 23) in the provided period, calculated from the base date. |
| getPeriodMinutes(period, base date) |
|
The number of minutes (maximum 59) in the provided period, calculated from the base date. |
| getPeriodSeconds(period, base date) |
|
The number of seconds (maximum 59) in the provided period, calculated from the base date. |
Locales
A locale is a set of parameters that defines the user's language, country and any special variant preferences. Smart Flows uses these parameters to interpret or output data in a certain way.
You can find a list of supported locales on this page.
Here are a few examples of locales:
- en - English
- en_US - English (United States)
- nl - Dutch
- de_AT - German (Austria)
When using locales as arguments in functions, make sure to put them in between apostrophes, like 'en_US'.