Aggregate data by week and display first day of week

Use-case: I’m interested in aggregating my data by week. I could use the weekofyear() function to get week number, but I would rather to show display more user friendly such as the start date of each week.

Solution: Use trunc(timestamp, “D”) function to truncate each date down to the starting day of that week.

Step 1: Enter your custom expression.

select trunc([order_date]), “D”)

OR if your date/timestamp is a string type:

select trunc(cast([order_date_string] as timestamp), “D”)

Result: Your dates are now bucketed by week, and showing the start date for each week

Step 2: Prettify your dates

You can trim timestamps down to simple date values using the to_date() function:

You can also use display format to modify the timestamp to look like a more user friendly date:

Result:

3 Likes

The trunc() function has many time bucket options such as quarter, month, year, etc
Refer here for more details:

trunc(timestamp, string unit)

Purpose: Strips off fields from a TIMESTAMP value.

Unit argument: The unit argument value is case-sensitive. This argument string can be one of:

  • SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y: Year.
  • Q: Quarter.
  • MONTH, MON, MM, RM: Month.
  • WW, W: Same day of the week as the first day of the month.
  • DDD, DD, J: Day.
  • DAY, DY, D: Starting day of the week. (Not necessarily the current day.)
  • HH, HH12, HH24: Hour. A TIMESTAMP value truncated to the hour is always represented in 24-hour notation, even for the HH12 argument string.
  • MI: Minute.
2 Likes

To adjust this calculation to show another day of the week (i.e. last of the week = Saturday), you can use the interval argument as part of the calculation:

to_date(trunc(cast([order_date_string] as timestamp),"D") + interval 5 days)