# Date formulas

Video guide: Learn how to transform dates with formula mode

Date and datetime formulas enable you to work with date and time datapills. These formulas are added to the allowlists for Ruby methods (opens new window), and therefore not all Ruby methods are supported. Reach out to us (opens new window) to add additional formulas to the allowlist.

# Date arithmetic

Use the following keywords to perform arithmetic with date and datetime data:

  • seconds
  • minutes
  • days
  • months
  • years

When combined with a formula, you can perform addition and subtraction.

# Sample usage

Date Arithmetic Output
"2020-01-01".to_date + 2.days "2020-01-03"
"2020-01-01".to_date - 2.days "2019-12-30"
"2020-01-01".to_date + 2.months "2020-03-01"
"2020-01-01".to_date - 2.months "2019-11-01"
"2020-01-01".to_date + 2.years "2022-01-01"
"2020-01-01".to_date - 2.years "2018-01-01"

# now

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

# Sample usage

Formula Result
now "2022-02-01T07:00:00.000000-08:00"
now + 8.hours "2022-02-01T15:00:00.000000-08:00"
now + 2.days "2022-02-03T07:00:00.000000-08:00"

# How it works

The formula calculates the timestamp when the job is processed. Each step using this formula returns the timestamp at which the step runs.

Output datapill

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

# See also

  • today: Returns the date at runtime.
  • in_time_zone: Converts a time value to a different time zone.

# today

Returns the date at runtime in US Pacific Time Zone.

# Sample usage

Formula Result
today "2022-02-01"
today + 8.hours "2022-02-01T15:00:00.000000-08:00"
today + 2.days "2022-02-03"

# How it works

The formula calculates the timestamp when the job is processed. Each step using this formula returns the date at which the step runs.

Output datapill

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

# See also

  • now: Returns the time and date at runtime.
  • in_time_zone: Converts a time value to a different time zone.

# from_now

Returns a 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

Formula Result
30.seconds.from_now "2022-02-01T07:00:30.000000-08:00"
2.months.from_now "2022-04-01T07:00:00.000000-08:00"
3.days.from_now "2022-02-04T07:00:00.000000-08:00"

# How it works

The formula calculates the current timestamp and offsets by a specified time duration. This timestamp is calculated when the job is processed. Each step using this formula returns a timestamp.

UNITS

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

# See also

  • ago: Returns an earlier timestamp by a specified time duration.
  • now: Returns the time and date at runtime.
  • today: Returns the date at runtime.

# 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

Formula Result
2.months.ago "2020-10-04 14:45:29 -0700"
3.days.ago "2020-12-01 14:45:29 -0700"
30.seconds.ago "2020-12-04 14:15:29 -0700"

# How it works

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

Units

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

# See also

  • from_now: Returns a future timestamp by a specified time duration.
  • now: Returns the time and date at runtime.
  • today: Returns the date at runtime.

# wday

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

# Syntax

Date.wday

  • Date - A date or datetime datatype.

# Sample usage

Example Result
today.wday 4
"01/12/2020".to_date(format:"DD/MM/YYYY").wday 2

# How it works

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

Quicktip: Convert to date datatype

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

# See also

  • yday: Returns the day number of the year.
  • yweek: Returns the week number of the year.

# yday

Returns day number of the year.

# Syntax

Date.yday

  • Date - A date or datetime datatype.

# Sample usage

Example Result
today.yday 338
"2020-01-01".to_date(format:"YYYY-MM-DD").yday 1
"2020-02-01".to_date(format:"YYYY-MM-DD").yday 32

# How it works

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

Quicktip: Convert to date datatype

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

# See also

  • wday: Returns the day number of the week.
  • yweek: Returns the week number of the year.

# yweek

Returns week number of the year.

# Syntax

Date.yweek

  • Date - A date or datetime datatype.

# Sample usage

Example Result
today.yweek 49
"2020-01-01".to_date(format:"YYYY-MM-DD").yweek 1
"2020-02-01".to_date(format:"YYYY-MM-DD").yweek 5

# How it works

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

Quicktip: Convert to date datatype

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

# See also

  • wday: Returns the day number of the week.
  • yday: Returns the day number of the year.

# 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

Formula Result
today.to_time.beginning_of_hour "2020-12-02T16:00:00.000000-07:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_hour "2020-06-01T01:00:00.000000+00:00"
"2020-06-01".to_time.beginning_of_hour "2020-06-01T00:00:00.000000+00:00"

# 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

Formula Result
today.beginning_of_day "2020-12-02T00:00:00.000000-07:00"
"2020-06-01".to_date.beginning_of_day "2020-06-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_day "2020-06-01T00:00:00.000000+00:00"

# beginning_of_week

Returns date for the start of the week (Monday) for a given date/timestamp.

# Syntax

Date.beginning_of_week

  • Date - An input date or datetime.

# Sample usage

Formula Result
today.beginning_of_week "2020-11-30T00:00:00.000000+00:00"
"2020-06-01".to_date.beginning_of_week "2020-06-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_week "2020-06-01T00:00:00.000000+00:00"

# 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

Formula Result
today.beginning_of_month "2020-12-01T00:00:00.000000+00:00"
"2020-06-01".to_date.beginning_of_month "2020-06-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_month "2020-06-01T00:00:00.000000+00:00"

# 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

Formula Result
today.beginning_of_year "2020-01-01T00:00:00.000000+00:00"
"2020-06-01".to_date.beginning_of_year "2020-01-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_year "2020-01-01T00:00:00.000000+00:00"

# 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.end_of_month

  • Date - An input date or datetime.

# Sample usage

Formula Result
today.end_of_month "2020-12-31"
"2020-06-01".to_date.end_of_month "2020-06-30"
"2020-06-01T01:30:45.000000+00:00".to_time.end_of_month "2020-06-30T23:59:59.999999+00:00"

# 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

Formula Result
"2020-06-05T17:13:27.000000-07:00".strftime("%Y/%m/%d") "2020/06/05"
"2020-06-05T17:13:27.000000-07:00".strftime("%Y-%m-%dT%H:%M:%S%z") "2020-06-05T17:13:27-0700"
"2020-06-05T17:13:27.000000-07:00".strftime("%B %e, %l:%M%p") "June 5, 5:13 pm"
"2020-06-05T17:13:27.000000-07:00".strftime("%A, %d %B %Y %k:%M") "Friday, 05 June 2020 0:00"

# Parameters

As previously shown, each code (%B, %e, %I, for example) refers to a specific element of datetime. You can also add static text and punctuation, such as commas (,), slashes (/), and colons (:). Refer to the following list of frequently used codes:

Code Meaning Example
(2020-06-05T17:13:27.000000-07:00)
%Y Year with century 2020
%m Month with zero-prefix 06
%B Full month name June
%b Abbreviated month name Jun
%d Day of the month with zero-prefix 05
%e Day of the month without zero-prefix 5
%H Hour of the day (24-hour) 17
%k Hour of day without 0 prefix (24-hour) 17
%I (capital i) Hour of the day (12-hour) 05
%l (lowercase L) Hour of day without 0 prefix (12-hour) 5
%p AM or PM PM
%M Minute of the hour 13
%S Second of the minute 27
%L Millisecond of the second 000
%z Time zone offset from UTC -0700
%:z Time zone formatted offset from UTC -07:00
%Z Time zone abbrev. name UTC
%A Full day name Friday

To access the full list, check out the Ruby documentation (opens new window)

# 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.

# See also

  • to_date: Returns a date in date datatype.

# in_time_zone

Converts a date or datetime to a different timezone using timezone names from the IANA time zone database (opens new window). This formula will return a datetime.

# Syntax

Date.in_time_zone(format)

  • Date - An input date or datetime.
  • format - The target timezone. If not specified, this formula will return the timezone defined by the data center your Workato account is hosted in.

# Sample usage

Formula Result
today.in_time_zone("America/New_York") "2020-12-01T20:00:00.000000-04:00"
today.to_time.in_time_zone("America/New_York") "2020-12-01T20:00:00.000000-04:00"
"2020-06-01".to_time.in_time_zone "2020-05-31T20:00:00.000000-04:00"
"2020-06-01T01:30:45.000000+00:00".in_time_zone "2020-05-31T12:30:00.000000-05:00"

# dst?

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

# Syntax

Datetime.dst?

  • Datetime - An input date or datetime.

# Sample usage

Formula Result
today.dst? false
today.in_time_zone("America/New_York").dst? true
"2020-06-01".in_time_zone("America/New_York").dst? true
"2020-09-06T18:30:15.671720-05:00".dst? true

# Regions reference

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

Region Timezone to use in formula UTC zone DST offset?
International Date Line West Pacific/Midway UTC-11
Midway Island Pacific/Midway UTC-11
American Samoa Pacific/Pago_Pago UTC-11
Hawaii Pacific/Honolulu UTC-10
Alaska America/Juneau UTC-9
Pacific Time (US & Canada) America/Los_Angeles UTC-8
Tijuana America/Tijuana UTC-8
Mountain Time (US & Canada) America/Denver UTC-7
Arizona America/Phoenix UTC-7
Chihuahua America/Chihuahua UTC-7
Mazatlan America/Mazatlan UTC-7
Central Time (US & Canada) America/Chicago UTC-6
Saskatchewan America/Regina UTC-6
Guadalajara America/Mexico_City UTC-6
Mexico City America/Mexico_City UTC-6
Monterrey America/Monterrey UTC-6
Central America America/Guatemala UTC-6
Eastern Time (US & Canada) America/New_York UTC-5
Indiana (East) America/Indiana/Indianapolis UTC-5
Bogota America/Bogota UTC-5
Lima America/Lima UTC-5
Quito America/Lima UTC-5
Atlantic Time (Canada) America/Halifax UTC-4
Caracas America/Caracas UTC-4
La Paz America/La_Paz UTC-4
Santiago America/Santiago UTC-4
Georgetown America/Guyana UTC-4
Newfoundland America/St_Johns UTC-3:30
Brasilia America/Sao_Paulo UTC-3
Buenos Aires America/Argentina/Buenos_Aires UTC-3
Montevideo America/Montevideo UTC-3
Greenland America/Godthab UTC-3
Mid-Atlantic Atlantic/South_Georgia UTC-2
Azores Atlantic/Azores UTC-1
Cape Verde Is. Atlantic/Cape_Verde UTC-1
Dublin Europe/Dublin UTC-1
Lisbon Europe/Lisbon UTC+0
Edinburgh Europe/London UTC+0
London Europe/London UTC+0
Monrovia Africa/Monrovia UTC+0
UTC Etc/UTC UTC+0
Casablanca Africa/Casablanca UTC+1
Belgrade Europe/Belgrade UTC+1
Bratislava Europe/Bratislava UTC+1
Budapest Europe/Budapest UTC+1
Ljubljana Europe/Ljubljana UTC+1
Prague Europe/Prague UTC+1
Sarajevo Europe/Sarajevo UTC+1
Skopje Europe/Skopje UTC+1
Warsaw Europe/Warsaw UTC+1
Zagreb Europe/Zagreb UTC+1
Brussels Europe/Brussels UTC+1
Copenhagen Europe/Copenhagen UTC+1
Madrid Europe/Madrid UTC+1
Paris Europe/Paris UTC+1
Amsterdam Europe/Amsterdam UTC+1
Berlin Europe/Berlin UTC+1
Bern Europe/Zurich UTC+1
Zurich Europe/Zurich UTC+1
Rome Europe/Rome UTC+1
Stockholm Europe/Stockholm UTC+1
Vienna Europe/Vienna UTC+1
West Central Africa Africa/Algiers UTC+1
Bucharest Europe/Bucharest UTC+2
Cairo Africa/Cairo UTC+2
Helsinki Europe/Helsinki UTC+2
Kyiv Europe/Kiev UTC+2
Riga Europe/Riga UTC+2
Sofia Europe/Sofia UTC+2
Tallinn Europe/Tallinn UTC+2
Vilnius Europe/Vilnius UTC+2
Athens Europe/Athens UTC+2
Jerusalem Asia/Jerusalem UTC+2
Harare Africa/Harare UTC+2
Pretoria Africa/Johannesburg UTC+2
Kaliningrad Europe/Kaliningrad UTC+2
Istanbul Europe/Istanbul UTC+3
Minsk Europe/Minsk UTC+3
Moscow Europe/Moscow UTC+3
St. Petersburg Europe/Moscow UTC+3
Kuwait Asia/Kuwait UTC+3
Riyadh Asia/Riyadh UTC+3
Nairobi Africa/Nairobi UTC+3
Baghdad Asia/Baghdad UTC+3
Tehran Asia/Tehran UTC+3:30
Volgograd Europe/Volgograd UTC+4
Samara Europe/Samara UTC+4
Abu Dhabi Asia/Muscat UTC+4
Muscat Asia/Muscat UTC+4
Baku Asia/Baku UTC+4
Tbilisi Asia/Tbilisi UTC+4
Yerevan Asia/Yerevan UTC+4
Kabul Asia/Kabul UTC+4:30
Ekaterinburg Asia/Yekaterinburg UTC+5
Islamabad Asia/Karachi UTC+5
Karachi Asia/Karachi UTC+5
Tashkent Asia/Tashkent UTC+5
Sri Jayawardenepura Asia/Colombo UTC+5:30
Chennai Asia/Kolkata UTC+5:30
Kolkata Asia/Kolkata UTC+5:30
Mumbai Asia/Kolkata UTC+5:30
New Delhi Asia/Kolkata UTC+5:30
Kathmandu Asia/Kathmandu UTC+5:45
Astana Asia/Dhaka UTC+6
Dhaka Asia/Dhaka UTC+6
Almaty Asia/Almaty UTC+6
Urumqi Asia/Urumqi UTC+6
Rangoon Asia/Rangoon UTC+6:30
Novosibirsk Asia/Novosibirsk UTC+7
Bangkok Asia/Bangkok UTC+7
Hanoi Asia/Bangkok UTC+7
Jakarta Asia/Jakarta UTC+7
Krasnoyarsk Asia/Krasnoyarsk UTC+7
Beijing Asia/Shanghai UTC+8
Chongqing Asia/Chongqing UTC+8
Hong Kong Asia/Hong_Kong UTC+8
Kuala Lumpur Asia/Kuala_Lumpur UTC+8
Singapore Asia/Singapore UTC+8
Taipei Asia/Taipei UTC+8
Perth Australia/Perth UTC+8
Irkutsk Asia/Irkutsk UTC+8
Ulaanbaatar Asia/Ulaanbaatar UTC+8
Seoul Asia/Seoul UTC+9
Osaka Asia/Tokyo UTC+9
Sapporo Asia/Tokyo UTC+9
Tokyo Asia/Tokyo UTC+9
Yakutsk Asia/Yakutsk UTC+9
Darwin Australia/Darwin UTC+9:30
Adelaide Australia/Adelaide UTC+9:30
Canberra Australia/Melbourne UTC+10
Melbourne Australia/Melbourne UTC+10
Sydney Australia/Sydney UTC+10
Brisbane Australia/Brisbane UTC+10
Hobart Australia/Hobart UTC+10
Vladivostok Asia/Vladivostok UTC+10
Guam Pacific/Guam UTC+10
Port Moresby Pacific/Port_Moresby UTC+10
Magadan Asia/Magadan UTC+11
Srednekolymsk Asia/Srednekolymsk UTC+11
Solomon Is. Pacific/Guadalcanal UTC+11
New Caledonia Pacific/Noumea UTC+11
Fiji Pacific/Fiji UTC+12
Kamchatka Asia/Kamchatka UTC+12
Marshall Is. Pacific/Majuro UTC+12
Auckland Pacific/Auckland UTC+12
Wellington Pacific/Auckland UTC+12
Nuku'alofa Pacific/Tongatapu UTC+13
Tokelau Is. Pacific/Fakaofo UTC+13
Samoa Pacific/Apia UTC+13
Chatham Is. Pacific/Chatham UTC+13:45

# to_date

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

# Syntax

String.to_date(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, Workato will parse the input string automatically.

# Sample usage

Formula Result
"23-01-2020 10:30 pm".to_date(format: "DD-MM-YYYY") "2020-01-23"
"01-23-2020 10:30 pm".to_date(format: "MM-DD-YYYY") "2020-01-23"
"2020/01/23".to_date(format: "YYYY/MM/DD") "2020-01-23"

# 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, Workato will automatically parse the input string.

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

# See also

  • strftime: Returns datetime is a custom format.
  • to_time: Converts a string to an ISO timestamp.

# to_time

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

# Syntax

String.to_time(format: format)

  • String - An input string that describes a date or datetime.
  • format - (optional) The format of the user-defined datetime written as a string.

# Sample usage

Formula Result
"2020-04-02T12:30:30.462659-07:00".to_time(format: "%Y-%m-%dT%H:%M:%S") "2020-04-02T19:30:30.000+00:00"
"2020-04-02".to_time "2020-04-02T00:00:00.000+00:00"

# 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.

# See also

  • strftime: Returns datetime is a custom format.
  • to_date: This formula converts the date-like input into a date. Returns the date formatted as YYYY-MM-DD.

# Parameters

As previously shown, each code (%B, %e, %I, for example) refers to a specific element of datetime. You can also add static text and punctuation, such as commas (,), slashes (/), and colons (:). Refer to the following list of frequently used codes:

Code Meaning Example
(2020-06-05T17:13:27.000000-07:00)
%Y Year with century 2020
%m Month with zero-prefix 06
%B Full month name June
%b Abbreviated month name Jun
%d Day of the month with zero-prefix 05
%e Day of the month without zero-prefix 5
%H Hour of the day (24-hour) 17
%k Hour of day without 0 prefix (24-hour) 17
%I (capital i) Hour of the day (12-hour) 05
%l (lowercase L) Hour of day without 0 prefix (12-hour) 5
%p AM or PM PM
%M Minute of the hour 13
%S Second of the minute 27
%L Millisecond of the second 000
%z Time zone offset from UTC -0700
%:z Time zone formatted offset from UTC -07:00
%Z Time zone abbrev. name UTC
%A Full day name Friday

To access the full list, check out the Ruby documentation (opens new window)


# to_i

Convert datetime into epoch time. Returns an epoch time in UTC (+00:00).

# Syntax

Datetime.to_i

  • Datetime - An input datetime.

# Sample usage

Formula Result
today.to_time.to_i 1645660800
now.to_i 1645714000

# How it works

Converts the input datetime into an integer, it will return epoch time in seconds, not milliseconds. The output datetime will be converted to the UTC timezone (+00:00).

Converting between Epoch time to datetime

Convert time formats easily with Workato formulas.

# How to convert human readable time to epoch time

Use to_i to convert a datetime datapill to epoch time (in UTC). Learn more about how it works.

# How to convert epoch time to human-readable time

Use the following formula to convert an epoch time to human-readable datetime.

Note that the output will be in UTC timezone (+00:00).

"1970-01-01".to_time +Epoch time.seconds

If you plan to convert epoch time to a specific timezone, you must specify it with in_time_zone.

"1970-01-01".to_time.in_time_zone("US/Pacific") +Epoch time.seconds

Wrong datatype: undefined method `to_i`

Epoch time requires a datetime datapill. If you are using a date datapill, it will cause an error.

Use to_time to convert a date into a datetime before converting to epoch time.

# See also

  • to_time: Converts a string to an ISO timestamp.
  • to_date: This formula converts the date-like input into a date. Returns the date formatted as YYYY-MM-DD.
  • in_time_zone: Converts a time value to a different time zone.


Last updated: 5/7/2024, 9:41:44 PM