Extract subfields from date/datetime values

In a time-series report, you might want to group data by different time intervals such as (Day of Week, Day of Month, Month of Year, Hour of Day,…).

Currently, Holistics only support date-aggregation by Year, Quarter, Month, Week, Day, Hour, and Minute. Unfortunately, there are no Holistics Expression functions to calculate datetime values, so you can’t extract subfields from date fields.

Here are workarounds for some typical date/time functions that get subfields from date/datetime values.

Overall solution

The recommended way would be to create a custom dimension and apply the appropriate formula to get subfields from date/datetime values

Detail formulas

This section below uses Postgres dialect. If you do not use Postgres, please be mindful to adapt the syntax to fit your current database’s dialect.

hour_of_day

Function	extract(hour from timestamp_field)
Definition	get the hour of the timestamp (0-23)
Example	extract(hour from timestamp '2001-02-16 20:38:40'). Result: 20

day_of_week

Function	to_char (date_field, 'day')
Definition	get the Day name (Mon, Tue, Wed,…) from the original day
Example	to_char(date '2022-01-01','day'). Result: ‘saturday’

Notes: Here are some formats you can use to extract the day name:

• ‘DAY’ would return an uppercase name (‘SATURDAY’)
• ‘Day’ would return a lowercase name (‘Saturday’)
• ‘DY’ would return ‘SAT’
• ‘Dy’ would return ‘Sat’
• ‘dy’ would return ‘sat’

day_of_week_number

Function	extract(isodow from timestamp_field)
Definition	get the day of week number (0 - 6; Sunday is 0) (for timestamp values only)
Example	extract(isodow from timestamp '2001-02-17 20:38:40'). Result: 5

day_of_month

Function	extract(day from timestamp_field)
Definition	get the day of the month field (1-31)
Example	extract(day from timestamp '2001-02-17 20:38:40'). Result: 17

day_of_year

Function	extract(doy from timestamp_field)
Definition	get the day of the year (1-365/366) (for timestamp values only)
Example	extract(doy from timestamp '2001-02-17 20:38:40'). Result: 48

week_of_year

Function	extract(week from timestamp_field)
Definition	get the number of the week of the year that the day is in
Example	extract(week from timestamp '2001-02-17 20:38:40'). Result: 7
Note	By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for timestamp values only). Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year.

month_of_year

Function	extract(month from timestamp_field)
Definition	get the number of the month within the year (1-12)
Example	extract(month from timestamp '2001-02-17 20:38:40'). Result: 2

quarter_of_year

Function	extract(quarter from timestamp_field)
Definition	get the quarter of the year (1 - 4) that the day is in (for timestamp values only)
Example	extract(quarter from timestamp '2001-02-17 20:38:40'). Result: 1

Tips: How to use day-of-week as the Axis title that shows the names of the day in chronological order?

Assuming that, you have an order data model. This model has a Datetime dimension created_at that contains timestamps like 2022-11-01 09:41:31.

Model order {
  type: 'table'
  ...

  dimension created_at {
    type: 'datetime'
  }
  ...
}

Context: When using to_char to get the day of the week (Sun, Mon…), the results are not acting as an actual date but as a text label. Therefore, they won't get sorted that align week automatically.

Solution:

As a workaround, you could do some data transformation to get the day of the week as shown in the image below:

In the modeling layer, navigate to your model that has the date field used in the dataset. And then:

Step 1: Get the day_of_week_number from the original day

Create new Custom Dimension day_number to get the day_of_week_number(0-6; Sunday is 6)

The formula for this field is:

extract(isodow from {{ #THIS.created_at }}) - 1

Step 2: Get the day_of_week name from the original day

Create a new custom dimension order_day to get the day_of_week name (Monday, Tuesday…) ****by using the formula:

to_char({{ #THIS.order_created_at }}, 'Day')

Step 3: Concatenate 2 new fields together

Create a new custom dimension day_of_week to concatenate 2 above fields together

concat({{ #THIS.day_number }}::text,'-',{{ #THIS.order_day }} )

Finally, in the Dataset, you will need to use the days_of_week field in the X-Axis.