Date functions

Learn about date functions in the Digibee Integration Platform and how to use them.

Date functions are used to process, generate, and convert dates. They are available for connectors that support Double Braces expressions. To learn how to provide information to connectors using this resource, refer to Double Braces Functions.

FORMATDATE

Using Double Braces, you can combine this function with access to the input JSON element of a connector.

FORMATDATE formats date and time values, with optional support for locale and timezone handling.

Syntax

FORMATDATE(value, "origin-format", "destination-format", "locale-origin"?, "timezone-origin"?, "locale-destination"?, "timezone-destination"?)

Parameters marked with ? are optional and can be set to null.

Date format definition: dd/MM/yyyy. You can also use the keyword timestamp instead of a format string. The value will always be converted using ISO Zoned Date/Time.

Date and time format patterns

Refer to the Java DateTimeFormatter documentation for more details on date and time format patterns.

Common patterns:

Pattern	Description
y	Year (year-of-era)
u	Year
M	Month of year
d	Day of month
E	Day of week (text)
e, c	Day of week (number)
D	Day of year (1–365)
H	Hour (0–23)
h	Hour (1–12)
a	AM/PM marker
m	Minutes
s	Seconds

Input example

{
  "date": "10/10/2010 11:59:59",
  "date_no_time": "30/10/2010",
  "time_no_date": "11:12:13",
  "timestamp_date": 1564670039000,
  "date_time_utc": "2012-10-01T09:45:00.0000000+00:00"
}

Conversion examples

{
  "timezone_conversion": {{ FORMATDATE(message.date, "dd/MM/uuuu HH:mm:ss", "dd/MM/uuuu HH:mm:ss z", null, "GMT-3", null, "UTC") }},

  "simple_date": {{ FORMATDATE(message.date_no_time, "dd/MM/uuuu", "MM/dd/uuuu") }},

  "simple_time": {{ FORMATDATE(message.time_no_date, "HH:mm:ss", "ss:mm:HH") }},

  "date_from_timestamp": {{ FORMATDATE(message.timestamp_date, "timestamp", "dd/MM/uuuu HH:mm:ss") }},

  "simple_date_to_timestamp": {{ FORMATDATE(message.simple_date, "dd/MM/uuuu", "timestamp") }},

  "iso_date_time": {{ FORMATDATE(message.timestamp_date, "timestamp", null) }},

  "date_month_name_pt_br": {{ FORMATDATE(NOW(), "timestamp", "dd/MMMM/uuuu", null, null, "pt-BR", null) }},

  "date_time_utc": {{ FORMATDATE(message.date_time_utc, "uuuu-MM-dd'T'HH:mm:ss.SSSSSSSXXX", "dd/MM/uuuu HH:mm:ss") }}
}

NOW

Using Double Braces, you can combine this function with access to the input JSON element of a connector.

NOW returns the current date and time as a timestamp in milliseconds.

Syntax

NOW()

Usage examples

{
  "now": {{ NOW() }},

  "currentDate": {{ FORMATDATE(NOW(), "timestamp", "uuuuMMdd", null, "GMT-3") }},

  "currentTime": {{ FORMATDATE(NOW(), "timestamp", "HHmmss", null, "GMT-3") }},

  "tomorrow": {{ FORMATDATE(TOLONG(SUM(NOW(), 86400000)), "timestamp", "uuuuMMdd") }},

  "time5minBefore": {{ TOLONG(SUBTRACT(NOW(), TOLONG("300000"))) }}
}

SUMDATE

Using Double Braces, you can combine this function with access to the input JSON element of a connector.

SUMDATE adds or subtracts a given amount of time from a date, based on a specified time unit.

Syntax

SUMDATE(milliseconds:number, unit:string, value:string)

Parameters:

• milliseconds — the input date as a Unix timestamp.

• unit — the time unit to apply. Accepted values: hour, minute, second, day, month, and year.

• value — the amount of time to add (use a negative number to subtract).

• zoneId — (optional) the timezone of the input date. Defaults to UTC.

Accepted timezones

• Australia/Darwin

• Australia/Sydney

• America/Argentina/Buenos_Aires

• Africa/Cairo

• America/Anchorage

• America/Sao_Paulo

• Asia/Dhaka

• Africa/Harare

• America/St_Johns

• America/Chicago

• Asia/Shanghai

• Africa/Addis_Ababa

• Europe/Paris

• America/Indiana/Indianapolis

• Asia/Kolkata

• Asia/Tokyo

• Pacific/Apia

• Asia/Yerevan

• Pacific/Auckland

• Asia/Karachi

• America/Phoenix

• America/Puerto_Rico

• America/Los_Angeles

• Pacific/Guadalcanal

• Asia/Ho_Chi_Minh

Example

To add 10 seconds to a timestamp:

{
  "test": {{ SUMDATE(1599368565518, "SECOND", 10) }}
}

Expected result:

{ 1599368565528 }

TOISODATE

Using Double Braces, you can combine this function with access to the input JSON element of a connector.

TOISODATE converts a date and time value to ISO Date format, with optional locale and timezone support.

Syntax

TOISODATE(value, "formatSource", "formatDestination", "locale"?, "timezone"?)

Parameters marked with ? are optional and can be set to null.

Date format definition: dd/MMMM/yyyy HH:mm:ss. You can also use the keyword timestamp instead of a format string. If no timezone is specified, UTC will be used by default.

Input example

{
  "date": "10/10/2010 11:59:59",
  "date_with_tz": "10/10/2010 11:59:59 GMT-03:00",
  "localized_date_with_tz": "10/Outubro/2010 11:59:59 GMT-03:00",
  "timestamp_date": "1564670039000",
  "date_no_time": "30/09/2018"
}

Conversion examples

{
  "forced_utc_no_locale": {{ TOISODATE(message.date, "dd/MM/uuuu HH:mm:ss", null, "UTC") }},

  "inferred_tz_no_locale": {{ TOISODATE(message.date_with_tz, "dd/MM/uuuu HH:mm:ss z") }},

  "localized_date_with_tz": {{ TOISODATE(message.localized_date_with_tz, "dd/MMMM/uuuu HH:mm:ss z", "pt-BR") }},

  "date_generated_from_timestamp": {{ TOISODATE(message.timestamp_date, "timestamp") }},

  "iso_datetime_from_date_only": {{ TOISODATE(message.date_no_time, "dd/MM/uuuu", null, "GMT-3") }}
}

DIFFDATE

DIFFDATE calculates the difference between two dates.

Syntax

DIFFDATE(timestamp1, timestamp2, "timeUnit")

Parameters:

• timestamp1, timestamp2 — both dates must be provided in timestamp format.

• timeUnit — accepted values: year, month, day, hour, minute, second, and millisecond.

The calculation is performed as: timestamp2 - timestamp1.

Notes:

• If the difference between the two dates is less than 1 unit, the result will be 0.

• If timestamp1 is greater than timestamp2, the function returns a negative value.

Input example

{
  "timestamp1": "1550458800000",
  "timestamp2": "1613617200000"
}

Application examples

{
  "years": {{ DIFFDATE(message.timestamp1, message.timestamp2, "year") }},

  "months": {{ DIFFDATE(message.timestamp1, message.timestamp2, "month") }},

  "hours": {{ DIFFDATE(message.timestamp1, message.timestamp2, "hour") }}
}