Skip to main content

How to Customize Weeks

Requirement Background

Sometimes regular weeks (Monday to Sunday) cannot meet our business scenarios, and we need to customize weeks to achieve this.

For example: How to implement Thursday to Wednesday as a week?

Implementation Methods

Regular Datasets

  1. First, understand the function dayofweek([Date]) to get the day of the week and the function weekofyear([Date]) to get the week number.

In Spark, a week runs from Sunday to Saturday, which is 1 to 7 (as shown in the figure below). To get regular weekdays, you need dayofweek([Date])-1.

image.png

  1. To implement Thursday to Wednesday as a week, Thursday, Friday, Saturday, and Sunday of each week should be postponed to the next week.
case when dayofweek([Date]) in (1,5,6,7) 
then WEEKOFYEAR([Date])+1 
else WEEKOFYEAR([Date]) 
end

image.png

  1. Final result: It can be seen that according to standard weeks (Monday to Sunday, which is 1 to 0), Thursday to Wednesday as a week has been achieved.

image.png

High-Performance Datasets

  1. Use the function toDayOfWeek([Date]) to first get which day of the week the date is.

toDayOfWeek converts Date or DateTime to a UInt8 number (Monday is 1, Sunday is 7) containing the day number of the week.

  1. Use the function to get which week the date belongs to.

toWeek(date[,mode]) returns the week number of Date or DateTime. The two-parameter form can specify whether the week starts from Sunday or Monday, and whether the return value should be in the range 0 to 53 or 1 to 53. If the mode parameter is omitted, the default mode is 0.

  1. Make a judgment. If you want to implement Thursday-Wednesday as a custom week, then Thursday, Friday, Saturday, and Sunday of each week should be postponed to next week.
case when [Return Day of Week] in(4,5,6,7) then ([Normal Week]+1) else [Normal Week] end
  1. Effect as follows:

image.png

【Notes】: The initial date field must be Date or DateTime type. If not, you need to convert it first.

Date and date time format definitions for toDate/toDateTime functions: YYYY-MM-DD or YYYY-MM-DD hh:mm:ss