Date Operators¶

Date operators allow you to handle information concerning time. You can use them to set or calculate a moment in time, determine the number of days in between two dates or format how a date should be displayed.

ISO8601 Format¶

ISO8601 is the international standard format for date or a date and time (wikipedia). The values are ordered from the largest unit of time to the smallest: year, month, day, hour, minute and second.

Date¶

A date is given as YYYY-MM-DD, for example 2016-02-29 (the founding date of Atfinity AG).

Time and Date¶

If you need a date with a time, the format would be YYYY-MM-DDThh-mm-ss, so a date and a time separated by a T. For example1999-06-25T09:52:08.

Using ISO8601¶

If dates are given to Atfinity as text, they always need to be in ISO8601 format. If a function in Atfinity returns a date, it will always be in ISO8601 format. If you want to return anything from Atfinity in a different format you can use FORMAT_DATE to format the output to your liking.

Counting elapsed time¶

DAYS_BETWEEN, HOURS_BETWEEN, DAYS_SPENT_IN_STATE and HOURS_SPENT_IN_STATE all count elapsed time the same way, and all take the same optional named arguments to narrow what counts:

Argument	Default	Effect
`exclude_weekends`	`false`	Leaves out every Saturday and Sunday.
`exclude_days`	nothing	Leaves out each date in the list it is given.
`exclude_before_time`	the beginning of the day	Counts nothing earlier than this time on any day, as `HH:MM:SS`.
`exclude_after_time`	the end of the day	Counts nothing later than this time on any day, as `HH:MM:SS`.
`day_length`	`24`	The hours that make up one day in the result, for `DAYS_BETWEEN` and `DAYS_SPENT_IN_STATE`.

DAYS_BETWEEN(
  start_date: c.received_at,
  end_date: NOW(),
  exclude_weekends: true,
  exclude_days: LIST('2024-12-25', '2024-12-26'),
  exclude_before_time: '09:00:00',
  exclude_after_time: '17:00:00',
  day_length: 8
)

This returns the number of eight-hour working days between the two moments, counting only 09:00 to 17:00 on days that are neither a weekend nor one of the two listed holidays.

Where you pass the dates yourself, a start value that carries no time counts from the beginning of its day, and an end value that carries no time counts to the end of its day. DAYS_BETWEEN('2024-05-08', '2024-05-10') is therefore 3, and a start and an end on the same date is 1 rather than 0. Give both values a time to count from moment to moment instead.

A value written with a UTC offset is converted before it is counted, so two values that name the same moment in different offsets count as no time apart. Day boundaries, and the times in the table above, follow your time zone.