Date formulas

AppConnect supports a variety of date and datetime formulas. Formulas in AppConnect are whitelisted Ruby methods, and therefore not all Ruby methods are supported. You can always reach out to us to add additional formulas to the whitelist.

Syntax and functionality for these formulas are generally unchanged. Take note that most formulas will return an error and stop the job if it tries to operate on nulls (expressed as nil in Ruby), except for present?, presence and blank?.

User settings within AppConnect cannot be edited to alter the time zone, although​​ users can utilize formulas to change the output to different time zones as applicable

Basics

now

Returns the time and date at runtime in US Pacific Time Zone.

Sample usage

How it works

The formula calculates the timestamp when the a job is being processed. Each step using this formula will return the timestamp at which the step runs.

Output datapill

If you only want the date without the time, try using the today formula instead.

today

Returns the date at runtime in US Pacific Time Zone.

Sample usage

How it works

The formula calculates the timestamp when the a job is being processed. Each step using this formula will return the timestamp at which the step runs.

Output datapill

If you want the date and time, try using the now formula sinstead.

from_now

Returns an future timestamp by a specified time duration. The timestamp is calculated at runtime.

Syntax

Unit.from_now

• Unit - A time value to offset.

Sample usage

How it works

The formula calculates the current timestamp and offsets by a specified time duration. This timestamp is calculated when the a job is being processed. Each step using this formula will return a timestamp for each step that runs.

Units

You can use any of these units: seconds, minutes, hours, days, months, or years.

ago

Returns an earlier timestamp by a specified time duration. The timestamp is calculated at runtime.

Syntax

Unit.ago

• Unit - A time value to offset.

Sample usage

How it works

The formula calculates the current timestamp and offsets by a specified time duration. This timestamp is calculated when the a job is being processed. Each step using this formula will return a timestamp for each step that runs.

Units

You can use any of these units: seconds, minutes, hours, days, months, or years.

wday

Returns day of the week. Sunday returns 0, monday returns 1.

Syntax

Date.wday

• Date - A date or datetime datatype.

Sample usage

How it works

The formula calculates the current day when the a job is being processed. The day of the week is converted into an integer output. Sunday = 0, monday = 1.

Quickip: Convert to date datatype

This formula only works with date or datetime datatype. Use to_date to convert a string into a date datatype.

yday

Returns day number of the year.

Syntax

Date.yday

• Date - A date or datetime datatype.

Sample usage

How it works

The formula calculates the current day when the a job is being processed. The day of the year is converted into an integer output.

Quickip: Convert to date datatype

This formula only works with date or datetime datatype. Use to_date to convert a string into a date datatype.

yweek

Returns week number of the year.

Syntax

Date.yweek

• Date - A date or datetime datatype.

Sample usage

How it works

The formula calculates the current day when the a job is being processed. The week of the year is converted into an integer output.

Quickip: Convert to date datatype

This formula only works with date or datetime datatype. Use to_date to convert a string into a date datatype.

Date arithmetics

We can make use of certain keywords such as days, months, years, minutes and seconds to perform date arithmetics to add or subtract days, months, years, minutes and seconds from dates and datetimes.

Sample usage

Getting first/last timestamp of the current/next periods

Using a combination of date formulas, and date arithmetics, we can easily obtain the first and last days of a current or subsequent time period using some helper formulas. Not all time periods are supported just yet.

beginning_of_hour

Returns datetime for top-of-the-hour for a given datetime.

Syntax

Datetime.beginning_of_hour

• Datetime - An input datetime.

Sample usage

beginning_of_day

Returns datetime for midnight on date of a given date/datetime.

Syntax

Date.beginning_of_day

• Date - An input date or datetime.

Sample usage

beginning_of_week

Returns date of the previous monday for a given date/datetime.

Syntax

Date.beginning_of_week

• Date - An input date or datetime.

Sample usage

beginning_of_month

Returns first day of the month for a given date/datetime.

Syntax

Date.beginning_of_month

• Date - An input date or datetime.

Sample usage

beginning_of_year

Returns first day of the year for a given date/datetime.

Syntax

Date.beginning_of_year

• Date - An input date or datetime.

Sample usage

end_of_month

Returns last day of the month for a given date/datetime. This formula will return a date or datetime based on the input data.

Syntax

Date.beginning_of_month

• Date - An input date or datetime.

Sample usage

Display conversion

strftime

Returns a datetime input as a user-defined string.

Syntax

Date.strftime(format)

• Date - An input date or datetime.

• format - The format of the user-defined datetime written as a string.

Sample usage

Parameters

As shown above, each code (%B, %e, %I etc.) refers to a specific element of datetime. Static text and punctuation can also be added, such as commas (,), slashes (/), and colons (:). Here's a list of commonly used codes in AppConnect:

How it works

Allows the user to define a datetime format. Returns the datetime input in the specified format.

Input datatype

The input must be a date or datetime datatype. You can use the to_date formula to convert a string into a date datatype.

in_time_zone

Converts a time value to a different timezone. This formula will return a date or datetime based on the input data.Syntax

Date.in_time_zone(format)

• Date - An input date or datetime.

• format - (optional) The target timezone. If not specified, this formula will return the timezone defined by your AppConnect accounts.

Sample usage

How it works

This formula uses the list of timezone names from the IANA time zone database. The output will be an equivalent time in a different timezone.

dst?

Returns true if the input datatime is within Daylight Savings Time.

Syntax

Datetime.dst?

• Datetime - An input date or datetime.

Sample usage

Regions reference

Refer to the following table for the timezone name to use in the formula.

Converting datetime to date

To convert a date data type into a datetime data, or vice versa, use the to_date or to_time formulas.

to_date

This formula converts the input data into a date. Returns the date formatted as YYYY-MM-DD.

Syntax

String.first(format: format)

• String - An input datetime or a string that describes a date or datetime.

• format - (optional) The date format of the input written as a string. If not specified, AppConnect will parse the input string automatically.

Sample usage

How it works

Converts the input data into a date datatype.

Input data best practice

It is recommended to specify the input data format. If the format is not specified, AppConnect will automatically parse the input string.

The input string must resemble a date for this formula to work.

to_time

Converts a string to an ISO timestamp. The response will use the UTC timezone (+00:00).

Syntax

String.to_time

• String - An input string that describes a date or datetime.

Sample usage

How it works

Converts the input string into a datetime datatype. The output datetime will be converted to the UTC timezone (+00:00).

Autofill time

If the input data does not include the time, the output will default to 00:00:00.000000 +00:00.

Conditionals

blank?

This formula checks the input string and returns true if it is an empty string or if it is null.

Syntax

Input.blank?

• Input - An input datapill. It can be a string, number, date, or datetime datatype.

Sample usage

How it works

If the input is null or an empty string, the formula will return false. For any other data, it returns true.

present?

This formula will check the input and if there is a value present, it will return true. If the input is nil, an empty string or an empty list, the formula will return false.

Syntax

Input.present?

• Input - An input datapill. It can be a string, number, date, or list datatype.

Sample usage

How it works

If the input is null, an empty string or an empty list, the formula will return false. For any other data, it returns true.

Evaluating a list with nil values

• Only an empty list will return false.

[].present? returns false.

• A list with nil and empty string will return true.

[nil,""].present? returns true.

presence

Returns the data if it exists, returns nil if it does not.

Syntax

Input.presence

• Input - An input datapill. It can be a string, number, date, or datetime datatype.

Sample usage

How it works

If the input is null or an empty string, the formula will return nil. For any other data, it returns the orignal input data.