Dates & Time

Smart Data modification - Date, Time and Datetime and how to tweak it your way

Locoia offers a wide array of pre-built date wildcards that are ready to be used as well as advanced filters that allow you to tailor dates according to your needs - meaning, modify, adjust and change them. Generally, you can pretty much wrangle (play) with date, time and datetimes in any way you may need.

Date wildcards ready to be used

The below date wildcards work immediately for your usage in any input field of a flow. NOTE: You cannot have a period at the end of the sentence.

All ready to use wildcards and example dates based on assuming today's date is 2019-06-01 09:09:01 :

• Current date:

  • {{ datetime }} → 2019-06-01 09:09:01 (UTC datetime stamp)

  • {{ date }} → 2019-06-01

  • {{ date_de }} → 01.06.2019

  • {{ date_us }} → 06/01/2019

• Current date - 7 days; so the date of seven days ago

  • {{ date_de_7 }} → 24.05.2019

  • {{ date_utc_7 }} → 2019-05-24

  • {{ date_us_7 }} → 05/24/2019

• Current date - 30 days; so the date of thrifty days ago

  • {{ date_de_30 }} → 01.05.2019

  • {{ date_utc_30 }} → 2019-05-01

  • {{ date_us_30 }} → 05/01/2019

• Current date - 180 days; so the date of 180 days ago

  • {{ date_de_180 }} → 03.12.18

  • {{ date_utc_180 }} → 2018-12-03

  • {{ date_us_180 }} → 12/03/2018

• Current datetime in UTC:

  • {{ time }} → 09:09

• Current UNIX time in seconds in UTC:

  • {{ unix_timestamp }} → 1622073600

Date time examples

Today 7 days ago in unix time with sends, e.g. need for HubSpot:

{{ unix_timestamp *1000 - 60 * 60 * 24 * 7 * 1000 }}

Advanced date, time and datetime handling

To provide even more flexibility than wildcards, you can use the convert_datetime function. It essentially allows you to output any kind of date or time format as you wish.

As above your code needs to go in between two {{ }} curly brackets and we borrow the logic from Jinja2 filters.

The logic foes as follows: Enter your date in any commonly known format you wish, e.g. UTC, German format, US format in single brackets ' within the curly brackets. Then enter a so called pipe on Mac OS (alt option + 7) on a PC (ctrl + alt + <) oder auf deutsch (strg + alt + <). Then include the word convert_datetime() and within it the date format as you desire in single quotes ' . E.g. below you see %Y--%m--%d where each letter immediately followed by the % sign, indicates which of your provide date's information you want to pass:

• %Y is the year

• %m is the month

• %d is the day

The double dashes -- are included for the user to be displayed:

{{ '12/17/2019' | convert_datetime('%Y-%m-%d') }}

The above expression results in 2019--12--17 as a result. This logic may look a little bit tricky at first, but it gives you full flexibility to use the below variables to output dates and times as you wish.

If you use a date wildcard as the basis, e.g. if you want to pull all data for the last 3 month from an API and the API takes a start and end date, this would look like this for the start date to get the date start date from 90 days ago:

{{ 'datetime' | convert_datetime('%Y-%m-%d', -90) }}

...and this to get today's date for the end date:

{{ 'datetime' | convert_datetime( '%Y-%m-%d' ) }}

If the date is dynamically coming e.g. from a loop, this would look like this, where loop1[0] is the row of e.g. a CSV to be changed:

{{ loop1[0] | convert_datetime('%Y-%m-%d %H:%M') }}

In order to ingest various types of dates e.g. when the day or year is in the first place. Just use the 'dayfirst' or 'yearfirst' in the corresponding positions

convert_datetime('%Y-%m-%d', -180, True, False) }}

convert_datetime('%Y-%m-%d', -180, yearfirst=true) }}

Positions:

1. Format you wish to convert the date to

2. Number of days you which to move the date to, relative to e.g. today if you pass in date or datetime as a wildcard. -180 is today's date half a year ago. +180 would today's date in half a year from now.

3. First Boolean (True) - determines if the day is shown first depending on the Boolean value. Example: for 12/11/2019 the date is interpreted as 11th of December 2019

4. Second Boolean(False) - determines if the year is shown first depending on the Boolean value. This means that for 12/11/17 the date is interpreted as 17th of November 2012 (and the interpretation here is clear since the 17th cannot be a month. If the number in the last place in this example is below 13, you also may want to pass in dayfirst or not, depending on the interpretation of the passed in date you desired.

Variables to be used in the convert_datetime functions first argument (first position where you format the date string you desire):

• %a: Returns the first three characters of the weekday, e.g. Wed.

• %A: Returns the full name of the weekday, e.g. Wednesday.

• %B: Returns the full name of the month, e.g. September.

• %w: Returns the weekday as a number, from 0 to 6, with Sunday being 0.

• %m: Returns the month as a number, from 01 to 12.

• %d: Returns the day as a number, from 01 to 31.

• %H: Returns the hours of the day 1-24

• %M: Returns the minutes of the hour 1-60

• %S: Returns the seconds of the minute 1-60

• %f: Returns the milliseconds

• %p: Returns AM/PM for time.

• %Y: Returns the year in four-digit format, e.g. 2021

• %y: Returns the year in two-digit format, that is, without the century. For example, "21" instead of "2021".

• %f: Returns microsecond from 000000 to 999999.

• %Z: Returns the timezone.

• %z: Returns UTC offset.

• %j: Returns the number of the day in the year, from 001 to 366.

• %W: Returns the week number of the year, from 00 to 53, with Monday being counted as the first day of the week.

• %U: Returns the week number of the year, from 00 to 53, with Sunday counted as the first day of each week.

Special Cases

1. If you get a date and always want the first of the month of the respective date, you can do this like this:

{{ '2019-09-03' | convert_datetime('1.%m.%Y') }}

This will give you the first of the month in German time format.

2. If your DateTime string includes a timezone adjustment at the end (e.g. "+01:00"), this part of the string will unfortunately not be read by the | convert_datetime function (as it's not a normal ISO string). Instead, it is possible to convert your DateTime to a UNIX timestamp, calculate the necessary adjustment in seconds, and then convert back to a normal DateTime string of your choosing. For a full example, search for "Timezone" on the Flow Marketplace to find a "Handling Datetime Strings with appended Timezone adjustment" Flow.