Types / Dates and Durations
Dates are a key part of your data, and there's a couple of important things you should know when using them in formula.
1. Dates are just numbers
Dates are represented as a number of milliseconds from
01/01/1970 00:00 UTC (because that's when computers started counting). Dates always include both a date and a time (down to the number milliseconds).
Here are some dates and their equivalent numbers:
01/01/1900 00:00 UTC = -2208988800000 01/01/1970 00:00 UTC = 0 12/03/2019 14:00 UTC = 1552399200000
As dates are just numbers you can easily compare two dates (the higher the number the later the date):
johns_birthday > janes_birthday
Because working out the correct number for a date would be difficult, there's a Date function to help with creating a new date (and time).
Date(2019, 3, 12) // 12/03/2019 00:00 UTC Date(2019, 3, 12, 14, 12) // 12/03/2019 14:12 UTC
If you have a date and time, but you really just want to compare days (and not consider the time aspect), then you can reset both dates to the start of the day.
StartOf(date1) - StartOf(date2) = exactly duration in days
Numbers are useful for working and manipulating dates, but most people won't understand a date represented as a number! For that, we can convert our date into a string format format.
FormatDate(Date(2019, 1, 1), 'DD/MM/YYYY') // "01/01/2019"
There are a number of other functions for dates - check out the full dates reference.
2. Durations are also numbers
A duration is stored in milliseconds - that enables you to be very precise about the how much time has passed.
1 second = 1000 1 minute = 60000 1 hour = 3600000 1 day = 86400000
Storing durations as a number allows you to easily perform basic math on them. For example, if you wanted to add two durations together, you can simply use:
duration1 + duration2
As dates are also numbers, you can also easily add a duration to a date:
date_of_purchase + duration_of_warranty
As these numbers can get quite large, there are a number of formula that make creating and using durations easier.
The following formula can be used to quickly create a duration (it's still just a number, but using these functions mean you don't have to calculate the number yourself):
Days(1) is equivalent to 86400000 Hours(2) is equivalent to 7200000 Mins(15) is equivalent to 900000 Seconds(10) is equivalent to 10000
If you need to get the number of days, hours or mins back from a duration you can use:
NumberOfDays(86400000) = 1 day NumberOfHours(3600000) = 1 hour NumberOfMins(60000) = 1 minute NumberOfSeconds(10000) = 10 seconds
There are a number of other functions for durations - check out the full duration reference.
3. Dates are always stored in UTC
Working with timezones can be difficult, so to keep things consistent we always store dates in the UTC timezone (as it never changes). If you want to output a time for a user in a particular timezone, you can use the timezone option in the FormatDate function.
FormatDate(date_1, "MM:SS", "America/Los_Angeles")