Date/Time functions¶
Date/time functions extract, transform or add elements to times or dates.
The following functions are available:
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 ( |
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 ( |
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 requires the parameter year to be or an integer or a nominal:
if the parameter is integer, 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.
if the parameter is nominal, 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 an integer or a nominal:
if the parameter is integer, 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.
if the parameter is nominal, 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 |
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
Falseas 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 |
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,
Truehas 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 ( |
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,
Truehas 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 ( |
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,
Truehas 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 ( |
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 ( |
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
Falseas 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 ( |
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 specifiedTrue.
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. |
extract |
A binary value ( |
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 ( |
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 ( |
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.
