Bucketing and extracting dates

  • 7 January 2022
  • 0 replies
  • 13 views

Badge
Every date & time column supports extraction and bucketing transformation.

Bucketing adjusts or "rounds" the date to the chosen granularity (hour, day, week, month, quarter, year). Let's look at a few examples, consider entry `2021/01/10 10:00:00`, choosing
  • hour yields `2021/01/10 10:00:00` (no change)
  • day yields `2021/01/10` (hours, minutes and seconds get truncated)
  • week yields `2021/01/04` (Monday of the same week)
  • quarter yields `2021/01/01` (beginning of Q1 2021)
  • year yields `2021/01/01` (first day of the year)

Extracting selects a desired part of the date and discards the rest. Again, using `2021/01/10 10:00:00` we can see how the common options transform the date to a single number:
  • hour yields `10` (0-24)
  • day of the week yields `7` (= Sunday, range from 1-7)
  • day of the month yields `10` (1-31)
  • day of the year yields `10` (1-365)
  • week yields `1` (1-53)
  • quarter yields `1` (1-4)
  • year yields `2021`
it's also useful to define custom aliases for these values as needed. For example mapping `1` to `Monday` and so on.

0 replies

Be the first to reply!

Reply