Date and Time Methods

Date and Time Methods


Overview

Matillion ETL offers a set of proprietary methods for use with Date and DateTime variables to ease the burden of handling the multiple formats and common calculations placed on the user. The new methods are described in brief below:

Method Description
.now() A new date object with a current server timestamp.
.add("<units>", <integer>) Add a specified length of time to a timestamp.
.format("<pattern>") Change the format of a timestamp.
.parse("<pattern>", <stringDate>) Parse a piece of a timestamp according to a given pattern.

Important Information

For the methods described in this article to work, you must use a Matillion ETL variable that is defined as a DateTime type and it must have a valid default value.

Each of the above methods can be used with DateTime variables set in Matillion through Manage Environment Variables (please refer to Using Variables for more details).

Using DateTime in Manage Job Variables window

The above methods are also useful in combination, such that a created date is immediately acted upon by another method. When using a variable entitled dt, for example:

${dt.now()}
${dt.add("days", 3)}

Can be written as:

${dt.now().add("days", 3)}  

Please Note

JavaScript expressions are only available to use as parameter values within Matillion ETL components. Any valid single JavaScript expression may be used; however, it is recommended that only simple expressions are used. All variables defined in the job and / or environment should also be available to reference.

Error

When entering values into a component's parameter editor, everything enclosed within ${ } of the literal string will be evaluated immediately. This validation process currently does not take variables into account and may assume the value is incorrect.


.now()

.now() creates a new Date object that takes the server's time and keeps it as a timestamp. The server time is determined by the instance Matillion ETL is running on. For example, in a Python component:

print dt.now()

.add("<units>", <integer>)

.add() uses a predefined DateTime and adds a specified time to it. The allowed units are:

  • seconds
  • minutes
  • hours
  • days
  • weeks
  • months
  • years

Where the integer argument then states the number of those units to add to the DateTime. Negative integers can also be given to take time away from a DateTime. For example, for a timestamp of "yesterday":

${dt.now().add("days", -1)}

.format("<pattern>")

.format() will take a timestamp and reformat it into one of two styles according to the following pattern arguments:

  • yyyy/MM/dd
  • HH:mm:ss.SSS

The two patterns returning a DateTime as either a date or a time. For example, to return the current date on the server:

${dt.now().format("yyyy/MM/dd")}

.parse("<pattern>", "<string>")

.parse() takes a string and converts it into a Date object.  For example, the string "2016" is turned into a date object with the format "yyyy" and is stored in the dt variable:

${dt.parse("yyyy", "2016")}