Date/Time functions#

Date/time functions extract, transform or add elements to times or dates.

The following functions are available:

addMonth.
addQuarter.
addWorkingDays.
addYear.
currDate.
currDatetime.
date.
datetime.
day.
hour.
isDate.
isDatetime.
isMonth.
isQuarter.
isTime.
isWeek.
minute.
month.
quarter.
second.
time.
timeZone.
week.
weekDay.
year.

Note

Date type valid formats (if the values aren’t this format, the function will return an error).

2019/10/12
12/10/2019
2019-10-12
12-10-2019
2019/Oct/12
12/Oct/2019
2019-Oct-12
12-Oct-2019

Week type valid formats (if the values aren’t this format, the function will return an error).

2019/W41
W41/2019
2019-W41
W41-2019

Month type valid formats (if the values aren’t this format, the function will return an error).

1492/10
10/1492
1492-10
10-1492
1492/Oct
1492-Oct
Oct/1492
Oct-1492

Quarter type valid formats (if the values aren’t this format, the function will return an error).

2019/Q3
Q3/2019
2019-Q3
Q3-2019

Datetime type valid formats (if the values aren’t this format, the function will return an error).

2019/10/12 17:33:45.120
12/10/2019 17:33:45.120
2019-10-12 17:33:45.120
12-10-2019 17:33:45.120
2019/Oct/12 17:33:45.120
12/Oct/2019 17:33:45.120
2019-Oct-12 17:33:45.120
12-Oct-2019 17:33:45.120
2019/05/22 17:33:45
2019/10/12 17:33:45
12/10/2019 17:33:45
2019-10-12 17:33:45
12-10-2019 17:33:45
2019/Oct/12 17:33:45
12/Oct/2019 17:33:45
2019-Oct-12 17:33:45
12-Oct-2019 17:33:45
2019/05/22 17:33
2019/05/22 17:33
2019/10/12 17:33
12/10/2019 17:33
2019-10-12 17:33
12-10-2019 17:33
2019/Oct/12 17:33
12/Oct/2019 17:33
2019-Oct-12 17:33
12-Oct-2019 17:33
2019/05/22 17:33

Time type valid formats (if the values aren’t this format, the function will return an error).

17:27:35
17:27:35.120
17:27

addMonth#

The addMonth function adds a given number of months to a date attribute.

Parameters

addMonth(date, nmonth)

Parameter	Description
date	The date attribute we want to add months to. The date parameter is mandatory.
nmonth	The number of months to add to the chosen date attribute. The nmonth parameter is mandatory.

Example - addMonth(date, nmonth)

The following example uses the Bike Sales dataset.

In this example, we want to add 2 months to the reported date, which is the attribute Date.
We type the following formula in the addmonth attribute: addMonth($"Date",2) and the attribute will be filled with the Date attribute’s values postponed by two months.
The results are: * In row 1 the value 2013-11-26 becomes 2014-01-26, * In row 2 the value 2015-11-26 becomes 2016-01-26, and so on.

addQuarter#

The addQuarter function adds a given number of quarters to a date attribute.

Parameters

addQuarter(date, nquarter)

Parameter	Description
date	The date attribute we want to add quarters to. The date parameter is mandatory.
nquarter	The number of quarters to add to the chosen date attribute. The nquarter parameter is mandatory. A quarter is made up of three months.

Example - addQuarter(date, nquarter)

The following example uses the Bike Sales dataset.

In this example, we want to add two quarters to the Date attribute.
We type the following formula in the addquarter attribute: addQuarter($"Date",2) and the attribute will be filled with the Date attribute’s values postponed by two quarters, that are six months.
The results are: * In row 1, the Date value 2013-11-26 becomes the addquarter 2014-05-26 value. * In row 2, the Date value 2015-11-26 becomes the addquarter 2016-05-26 value, and so on.

addWorkingDays#

The addWorkingDays function adds a given number of working days (excluding weekends) to a date attribute.

Parameters

addWorkingDays(date, nday)

Parameter	Description
date	The date attribute we want to add working days to. The date parameter is mandatory.
nday	The number of working days to add to the chosen date attribute. The nday parameter is mandatory.

Example - addWorkingDays(date, nday)

The following example uses the Bike Sales dataset.

In this example, we want to add five working days to the Date attribute.
We type the following formula in the addwd attribute: addWorkingDays($"Date",5) and the attribute will be filled with the Date attribute’s values postponed by five working days, which might be more than five days, as weekends are excluded by the function since it considers only working days, not calendar days.
The results are as follows: * In row 1, the Date value 2013-11-26 becomes 2013-12-03: the 26th of November was Tuesday, and the 3rd of December was Monday. Five working days have been added, and so on.

addYear#

The addYear function adds a given number of years to a date attribute.

Parameters

addYear(date, nyear)

Parameter	Description
date	The date attribute we want to add quarters to. The date parameter is mandatory.
nyear	The number of years to add to the chosen date attribute. The nyear parameter is mandatory.

Example - addYear(date, nyear)

The following example uses the Bike Sales dataset.

In this example, we want to add two years to the Date attribute.
We type the following formula in the addyear attribute: addYear($"Date",2) and the attribute will be filled with the Date attribute’s values postponed by two years.
The results are as follows:
- In row 1, the Date value 2013-11-26 becomes 2015-11-26 in the addyear attribute.
- In row 2, the Date value 2015-11-26 becomes 2017-11-26 in the addyear attribute.

currDate#

The currDate function returns the current date according to local or UTC settings.

Parameters

currDate(utc)

Parameter	Description
utc	A binary value (`True` / `False`) controlling if local or UTC settings must be used. Set as `False` as default. The binary parameter values (`True` / `False`) are case/sensitive.

Example - currDate()

In this example, we want to add a new column reporting the current date.
We add a new attribute, called Today, and we type the following formula: currDate() and the attribute will be filled with the current date (in this case, the example has been written on the 13th of April 2022).

currDatetime#

The currDatetime function returns the current datetime according to local or UTC settings.

Parameters

currDatetime(utc)

Parameter	Description
utc	A binary value (`True` / `False`) controlling if local or UTC settings must be used. Set as `False` as default. The binary parameter values (`True` / `False`) are case-sensitive.

Example - currDatetime()

In this example, we want to add a new column reporting the current date and time.
We add a new attribute, called Today, and we type the following formula: currDatetime() and the attribute will be filled with the current date and time(in this case, the example has been written on the 13th of April 2022 at 15:49, the time has an accuracy of milliseconds).

date#

The date function returns a column with all values equal to the date consisting of given year, month and day.

Hint

This function returns completely different results when working using the year parameter only. In this use case, the function works as a cast function; it expects the parameter year to be or an integer or a nominal:

in the integer case, the year parameter represents the number of days to add to the default Rulex date, which is 1899-12-30, in order to return the corresponding date.
in the nominal case, the year parameter is the date written in string form. Pay attention to the supported formats.

Parameters

date(year, month, day)

Parameter	Description
year	The integer attribute containing the year from which we want to cast a date. The year parameter is mandatory.
month	The integer attribute containing the month. Set to 0 as default.
day	The integer attribute containing the day. Set to 0 as default.

Example - date(year, month, day)

The following example uses the Instagram_data dataset.

In this example we want to retrieve the date by concatenating the year, month and day, to create an attribute containing the date posted.
Add a new attribute, called date, and type the following formula: date($"Year",$"Month",$"Day").
As we have specified not only the mandatory parameter, but also the optional parameters, the function concatenates the values to create a date.

Example - date(year)

The following example uses the Instagram_data dataset.

In this second example we want to specify the year parameter only, to retrieve the date after the number of days indicated in the year parameter, according to Rulex default date (1899-12-30).
Add a new attribute, called Rulex date, and type the following formula: date($"Year").
The results have to be read as follows: * In all the rows displayed in the screenshot, so from row 1 to row 18, the Year value is 2019, therefore the returned date, which is 2019 days after Rulex’s default date, is 1905-07-11.

datetime#

The datetime function returns in each row of the result the datetime value obtained by the composition of the date value contained in the date entry and the time value contained in the time entry.

Hint

This function returns completely different results when working using the date parameter only. In this use case, the function works as a cast function; it expects the parameter date to be or an integer or a nominal:

in the integer case, the date parameter represents the number of milliseconds to add to 1899-12-31 00:00:00.000, in order to return the corresponding datetime.
in the nominal case, the date parameter is the datetime written in string form. Pay attention to the supported formats.

Parameters

datetime(date, time)

Parameter	Description
date	The date, week, month, quarter, datetime attribute type from which we want to cast the datetime. The date parameter is mandatory.
time	The time or datetime attribute type from which we want to cast the datetime.

Example - datetime(date, time)

The following example uses the datetime dataset.

In this example, we want to fill a new attribute, called Datetime, with the date and time together.
We type the following formula: datetime($"Date",$"Time") and the attribute will be filled with the combination of the date and time.

day#

The day function returns the day value of the date.

Parameters

day(date)

Parameter	Description
date	The date, week, month, quarter, datetime attribute we want to use to cast the day. The date parameter is mandatory.

Example - day(date)

The following example uses the Bike Sales dataset.

In this example you want to extract only the day string from the Date attribute.
Add a new attribute, called newday, and type the following formula: day($"Date") and the attribute will be filled with the day value of the Date attribute.

hour#

The hour function returns the hour value of time.

Parameters

hour(time)

Parameter	Description
time	The datetime or time attribute from which we want to retrieve the time. The time parameter is mandatory.

Example - hour(time)

The following example uses the Instagram_data dataset.

In this example, we want to create a new attribute with the hour the posts were created, which is now contained in the Date Posted attribute.
We create a new attribute, called hour function, and write the following formula: hour($"Date Posted") and the attribute will be filled with the hour only.

isDate#

The isDate function checks whether the data type of the attribute specified in the string parameter is date.

Parameters

isDate(string, binary)

Parameter	Description
string	The nominal attribute to be tested. The string parameter is mandatory.
binary	If it is `True`, or not specified, results will be provided as boolean values (`True` / `False`), while if it is `False`, results will be provided in binary form (`1` / `0`). The binary parameter values (`True` / `False`) are case-sensitive.

Example - isDate(string, binary)

The following example uses the IsFunctions dataset.

In this example, we want to check if the first attribute is a Date attribute.
We add a new attribute, that we will call isdate, and write the following formula to fill it: isDate($"Date",False).
We wrote False as we want our results to be displayed in binary form.

If you want to delete the outliers, you can apply a filter to the isdate attribute.
In this example, as you can see, the value in row 7 of the Date attribute is not a date, so the function has returned a 0.
Here, we want to keep only those values which are of Date type, where the function has returned 1.
So, drag the isdate attribute onto the Post-filter area and uncheck the 0.
Then, click Apply and Make Persistent.

This is the final result after having post-filtered the isdate attribute.

isDatetime#

The isDatetime function checks whether the data type of the attribute specified in the string parameter is date.

Parameters

isDatetime(string, binary)

Parameter	Description
string	The nominal attribute to be tested. The string parameter is mandatory.
binary	If it is `True`, or not specified, results will be provided as boolean values (`True` / `False`), while if it is `False`, results will be provided in binary form (`1` / `0`). The binary parameter values (`True` / `False`) are case-sensitive.

Example - isDatetime(string, binary)

The following example uses the datetime dataset.

In this example, we want to check if the Date Posted attribute is a Datetime attribute.
We add a new attribute and write the following formula: isDatetime($"Date Posted").
As we haven’t specified the binary parameter, True has been set as default, so the results will be provided as boolean values and not binary values (0/1).

If you want to delete the outliers, you can apply a filter to the isdatetime attribute.
In this example, as you can see, the value in row 5 of the Date Posted attribute is not a date, so the function has returned a False.
Here, we want to keep only those values which are of Datetime type, where the function has returned True.
So, drag the isdatetime attribute onto the Post-filter area and uncheck the False. Then, click Apply and Make Persistent.

This is the final result after having post-filtered the isdatetime attribute.

isMonth#

The isMonth function checks whether the data type of the attribute specified in the string parameter is month.

Parameters

isMonth(string, binary)

Parameter	Description
string	The nominal attribute to be tested. The string parameter is mandatory.
binary	If the optional binary parameter is True, or not specified, results will be provided as boolean values (`True` / `False`), while if it is False, results will be provided in binary form (`1` / `0`). The binary parameter values (`True` / `False`) are case-sensitive.

Example - isMonth(string, binary)

The following example uses the Bike Sales dataset.

In this example, we want to check if the Month attribute is a month attribute.
We add a new attribute and write the following formula: isMonth($"Month").
As we haven’t specified the binary parameter, True has been set as default, so the results will be provided as boolean values.
As the values aren’t written in one of the formats above, the function has returned False, as this format is undoubtedly nominal.

isQuarter#

The isQuarter function checks whether the data type of the attribute specified in the string parameter is quarter.

Parameters

isQuarter(string, binary)

Parameter	Description
string	The nominal attribute to be tested. The string parameter is mandatory.
binary	If the optional binary parameter is True, or not specified, results will be provided as boolean values (`True` / `False`), while if it is False, results will be provided in binary form (`1` / `0`). The binary parameter values (`True` / `False`) are case-sensitive.

Example - isQuarter(string, binary)

The following example uses the Quarterly Sales dataset.

In this example, we want to check if the Quarter attribute is a quarter attribute.
We add a new attribute and write the following formula: isQuarter($"Quarter").
As we haven’t specified the binary parameter, True has been set as default, so the results will be provided as boolean values.
The function has returned False, because the values aren’t written in one of the formats above.

isTime#

The isTime function checks whether the data type of the attribute specified in the string parameter is time.

Parameters

isTime(string, binary)

Parameter	Description
string	The nominal attribute to be tested. The string parameter is mandatory.
binary	If the optional binary parameter is True, or not specified, results will be provided as boolean values (`True` / `False`), while if it is False, results will be provided in binary form (`1` / `0`). The binary parameter values (`True` / `False`) are case-sensitive.

Example - isTime(string, binary)

The following example uses the Flights in Brasil dataset.

In this example, we want to check if the Partida.Prevista attribute is a time attribute.
We add a new attribute and write the following formula: isTime($"Partida.Prevista",False).
The function has returned 0, because the values aren’t written in one of the formats above.

isWeek#

The isWeek function checks whether the data type of the attribute specified in the string parameter is week.

Parameters

isWeek(string, binary)

Parameter	Description
string	The nominal attribute to be tested. The string parameter is mandatory.
binary	If the optional binary parameter is True, or not specified, results will be provided as boolean values (`True` / `False`), while if it is False, results will be provided in binary form (`1` / `0`). The binary parameter values (`True` / `False`) are case-sensitive.

Example - isWeek(string, binary)

The following example uses the Turkish Calendar dataset.

In this example, we want to check if the WEEK_OF_YEAR attribute is a Week attribute.
Change the WEEK_OF_YEAR attribute type to nominal, as the string parameter needs to be nominal.
We add a new attribute, that we will call isweek, and write the following formula to fill it: isWeek($"WEEK_OF_YEAR",False).
We wrote False as we want our results to be displayed in binary form.
As the values of the WEEK_OF_YEAR attribute are not in the formats listed above, the function has returned only 0, which means false, setting the isweek attribute type as integer.

minute#

The minute function returns the minute value of time.

Parameters

minute(time)

Parameter	Description
time	The datetime or time attribute from which we want to retrieve the minute. The time parameter is mandatory.

Example - minute(time)

The following example uses the Instagram_data dataset.

Suppose that you don’t have already the Minute attribute in the dataset, and you want to retrieve the minute from the Date Posted attribute.
Add a new attribute, called New Minute, and type the following formula: minute($"Date Posted") and the attribute will be filled with the minute values of the Date Posted attribute.
You can use the existing Minute attribute to double-check the results.

month#

The month function returns the month value of date.

Hint

This function returns completely different results if the extract parameter is True. In this use case, the function works as a cast function; it expects the parameter date to be date:

date attribute is then cast to a month attribute.

Parameters

month(date, extract)

Parameter	Description
date	The date, week, month, quarter or datetime attribute if the extract parameter is False to retrieve the month value. If the extract parameter is True, all attribute types can be used. The date parameter is mandatory.
extract	A binary value (`True` / `False`) which is set as `False` as default. If `False`, the function returns the month only, if True, the function returns also the corresponding year, if the date attribute is a date or datetime attribute. The binary parameter values (`True` / `False`) are case-sensitive.

Example - month(date, extract)

The following example uses the Instagram_data dataset.

In this example, we want to retrieve the month and the year of the Date Posted attribute.
We add a new attribute, which is called Month Detail, and type the following formula: month($"Date Posted",True) and the attribute is filled not only with the month values, but also with the year values, as we specified True.

quarter#

The quarter function returns the first month of the corresponding quarter of the date attribute. The results will always be:

1, if the date attribute is in the first quarter, from January to March;
4, if the date attribute is in the second quarter, from April to June;
7, if the date attribute is in the third quarter, from July to September;
10, if the date attribute is in the fourth quarter, from October to December.

Hint

This function returns completely different results if the extract parameter is True. In this use case, the function works as a cast function; it expects the parameter date to be date:

date attribute is then cast to a quarter attribute.

Parameters

quarter(date, extract)

Parameter	Description
date	The date, week, month, quarter or datetime attribute if the extract parameter is False to retrieve the month value. If the extract parameter is True, all attribute types can be used. The date parameter is mandatory.
extract	A binary value (`True` / `False`) which is set as `False` as default. If `True`, the function returns the corresponding quarter. The binary parameter values (`True` / `False`) are case-sensitive.

Example - quarter(date)

The following example uses the Instagram_data dataset.

In this example, we want to retrieve the quarter of the Date Posted attribute.
We want to be displayed the first month of the date’s corresponding order, so the formula will be:quarter($"Date Posted").
The results can be read as follows: * From rows 1 to 13, the quarter of the year when the posts were published begins with 4, so it refers to the second quarter, beginning with April (month number 4) and ending with June.

Example - quarter(date, extract)

The following example uses the Instagram_data dataset.

In this example, we want to retrieve the quarter of the Date Posted attribute.
As we want the exact quarter, and not its first month, we need to specify the extract parameter as True. Add a new attribute, called quarter, and type the following formula:quarter($"Date Posted",True)
The result is a quarter type column filled with the date value cast to quarter type.

second#

The second function returns the seconds (values) of the time values.

Parameters

second(time)

Parameter	Description
time	The datetime or time attribute from which we want to retrieve the second. The time parameter is mandatory.

Example - second(time)

The following example uses the Instagram_data dataset.

In this example, we want to retrieve the seconds of the Date Posted attribute, in which the date and time are contained.
We add a new attribute, called second, and we type the following formula:second($"Date Posted") and the attribute is filled with the seconds of the Date Posted attribute.

time#

The time function composes a time starting from hours, minutes and seconds strings.

Hint

This function returns completely different results when working using the hour parameter only. In this use case, the function works as a cast function; it expects the parameter hour to be or an integer or a nominal:

in the integer case, the hour parameter represents the number of microseconds to add to 00:00:00.000, in order to return the corresponding time.
in the nominal case, the hour parameter is the time written in string form. Pay attention to the supported formats.

Parameters

time(hour, minute, second)

Parameter	Description
hour	The attribute containing the hour as an integer number. The hour parameter is mandatory.
minute	The attribute containing the minute as an integer number.
second	The attribute containing the second as a continuous number.

Example - time(hour, minute)

The following example uses the Instagram_data dataset.

In this example, we want to create a new attribute which merges the Hour and Minute attributes.
We add a new attribute, called Time, and type the following formula:time($"Hour",$"Minute") and the attribute is filled with the time.

timeZone#

The timeZone function returns the current timezone, i.e. the difference between local time and UTC time. The resulting type is time.

Parameters

timeZone()

No parameters are required in this formula.

Example - timeZone()

The following example uses the HR-employee-attrition dataset.

In this example, we want to add a new column containing the timezone this dataset has been modified.
We add a new attribute, called timezone, and type the following formula:timeZone() and the attribute is filled with the difference between local and UTC time, that is UTC +2.

week#

The week function returns the week integer value of an attribute containing a value which can lead to a date.

Hint

This function returns completely different results if the extract parameter is True. In this use case, the function works as a cast function; it expects the parameter date to be date:

date attribute is then cast to a week attribute.

Parameters

week(date, extract)

Parameter	Description
date	The date, week, month, quarter or datetime attribute type if the extract parameter is False to retrieve the month value. If the extract parameter is True, all attribute types can be used. The date parameter is mandatory.
extract	A binary value (`True` / `False`) which is set as `False` as default. The binary parameter values (`True` / `False`) are case-sensitive.

Example - week(date)

The following example uses the Instagram_data dataset.

In this example, we want to retrieve the week of the year when the pictures were posted.
We add a new attribute, called week, and type the following formula:week($"Date Posted") and the attribute will be filled with the corresponding week in integer value.

weekDay#

The weekDay function returns the day of the week as an integer for each value of date.

Parameters

weekDay(date, mondaystart)

Parameter	Description
date	The date, week, month, quarter or datetime attribute if the extract parameter is False to retrieve the weekday value. The date parameter is mandatory.
mondaystart	A binary (`True` / `False`) set as `True` as default, controlling if the numeration of the days starts from Monday (`True`) or Sunday (`False`). The binary parameter values (`True` / `False`) are case-sensitive.

Example - weekDay(date)

The following example uses the Instagram_data dataset.

In this example, we want to fill a new attribute with the weekday when the picture was posted.
We add a new attribute, called weekday, and type the following formula:weekDay($"Date Posted") and the attribute is filled with the corresponding integer value of the day of the week.
As we didn’t specify the extract parameter, it is set as True, so the first day of the week is Monday.
The results can be read as follows: * In row 1, the weekday of the Date Posted attribute is 7, so it is Sunday. * In row 3, the weekday of the Date Posted attribute is 5, so it is Friday, and so on.

year#

The year function returns the year value of an attribute containing a date.

Parameters

year(date)

Parameter	Description
date	The date, week, month, quarter or datetime attribute type from which you want to retrieve the month value. The date parameter is mandatory.

Example - year(date)

The following example uses the Instagram_data dataset.

In this example, imagine the Year attribute doesn’t exist, and we want to fill a new attribute with the year value of the Date Posted attribute.
Add a new attribute, called newyear, and type the following formula:year($"Date Posted") and the attribute will be populated with the year of the datetime attribute Date Posted.
You can double-check the results by comparing the column with the existing Year one.