Date & Time Formats

A lot of the date & time functions require a date, time, dateTime or period as input. While you'll often be providing a field or a calculated date, it is good to know how to specify these, as an invalid input will result in an error.

All of these are based on the ISO8601 standard. Note that not every specification of the standard is allowed.

With all of these formats, you need to specify valid dates, times, periods etc. You cannot use 2015-20-20 as a date as there is no 20th month. Or 05:90:30 as a time, since 59 is the maximum value for minutes.

Date

Dates are comprised of years, months and days, and their formatting is represented as follows:

  • yyyy means a year in 4 digits (0000-9999)
  • MM means a month in 2 digits (01-12)
  • dd means a day in 2 digits (01-31)

Note that you need to use the full digits. You cannot use 1 as January, you need to use 01. Same goes for days.

A value that represents a date may be formatted as follows:

  • yyyy-MM-dd (eg. 2015-09-30)
  • yyyy-MM (eg. 2015-09)
  • yyyy (eg. 2015)

Non-specified months are assumed to be January, and non-specified days are assumed to be the first day of the month. For example:

  • Formatting 2015 as a date will result in 2015-01-01
  • Formatting 2016-03 as a date will result in 2016-03-01

Time

Times are comprised of hours, minutes and seconds, and their formatting is represented as follows:

  • hh means an hour in 2 digits (00-23)
  • mm means a minute in 2 digits (00-59)
  • ss means a second in 2 digits (00-59)

Note that you need to use the full digits. So use 05 instead of 5, 02 instead of 2 etc.

A value that represents a time needs to be formatted as follows:

  • hh:mm:ss (eg. 05:30:21)

Since no time zone information is provided, all times specified like this are interpreted as local time. It is not recommended to use local time when dealing with other time zones, as 05:30:21 in Brussels is not the same as 05:30:21 in New York.

Time with time zone

When specifying a time, it may be useful to specify the time zone as well. Time zones are defined by specifying the UTC offset as follows:

  • hh:mm:ss-hh:mm for negative UTC offset (eg. 05:30:21-07:00)
  • hh:mm:ss+hh:mm for positive UTC offset (eg. 05:30:21+02:00)

It is also possible to provide UTC time (also known as Zulu time) by adding a Z at the end:

  • hh:mm:ssZ (eg. 05:30:21Z)

UTC time takes into account the current time zone. For instance, in Brussels, in winter, (which is UTC+1 in winter), 05:30:21Z is the same as 06:30:21. You can read more about UTC time here.

DateTime

A dateTime is a date and a time put together. Specifying a dateTime is done by putting a T in between the date and time. A value that represents a dateTime may be formatted as follows:

  • yyyy-MM-ddThh:mm:ss (eg. 2015-09-30T05:30:21)
  • yyyy-MM-ddThh:mm:ssZ for UTC time
  • yyyy-MM-ddThh:mm:ss-hh:mm for negative UTC offset (eg. 2015-09-30T05:30:21-07:00)
  • yyyy-MM-ddThh:mm:ss+hh:mm for positive UTC offset (eg. 2015-09-30T05:30:21+02:00)
  • yyyy-MM-dd (eg. 2015-09-30)
  • yyyy-MM (eg. 2015-09)
  • yyyy (eg. 2015)

Any omitted parameters will be set to their default value: month to January, day to 01 and all hours, minutes and seconds to 00.

Period (milliseconds)

A period is a length of time. It is defined by specifying a number of milliseconds. For instance:

  • 86,400,000 = 1 day
  • 1,145,700,000 = 12 days, 30 minutes and 15 seconds

Specifying periods in milliseconds is highly impractical (it's just how it's done in the background) so there are functions you can use to help you define your period, such as createPeriod.

Period (ISO)

Another method of specifying a period is using the ISO8601 standard, by using the createPeriodIso function. Instead of using milliseconds, you can specify each time element separately. For instance:

  • P1Y2M5W8DT4H15M3S = 1 year, 2 months, 5 weeks, 8 days, 4 hours, 15 minutes and 3 seconds

That is the full specification, but you can omit time elements:

  • P1YT1H = 1 year and 1 hour
  • P15DT5M = 15 days and 5 minutes
  • PT5M10S = 5 minutes and 10 seconds

It's important that you follow these rules when specifying a period in the ISO8601 standard:

  • Follow this notation: PxYxMxWxDTxHxMxS where
    • P tells the computer that you're defining a period (mandatory)
    • xY is the number (x) of years (optional)
    • xM is the number (x) of months (optional)
    • xW is the number (x) of weeks (optional)
    • xD is the number (x) of days (optional)
    • T tells the computer that the rest is about hours, minutes and seconds (optional)
    • xH is the number (x) of hours (optional)
    • xM is the number (x) of minutes (optional)
    • xS is the number (x) of seconds (optional)
  • If you want to use hours, minutes or seconds in your period definition, you need to add T before the first hour/minute/second element.
  • Stick to the order of the notation, also when leaving out time elements.
  • There are no limits to the value of a time element. For instance, you can define PT5000H for defining 5000 hours.