Skip to main content

Extract Subfields from Dates

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 time-related AQL 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 SQL formula to get subfields from date/datetime values

Detail formulas

info

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

Functionextract(hour from timestamp_field)
Definitionget the hour of the timestamp (0-23)
Example

extract(hour from timestamp '2001-02-16 20:38:40'). Result: 20

hour_of_day

day_of_week

Functionto_char (date_field, 'day')
Definitionget the Day name (Mon, Tue, Wed,…) from the original day
Example

to_char(date '2022-01-01','day'). Result: ‘saturday’

day_of_week

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

Functionextract(isodow from timestamp_field)
Definitionget 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

Functionextract(day from timestamp_field)
Definitionget the day of the month field (1-31)
Example

extract(day from timestamp '2001-02-17 20:38:40'). Result: 17

day_of_year

Functionextract(doy from timestamp_field)
Definitionget 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

Functionextract(week from timestamp_field)
Definitionget 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

Functionextract(month from timestamp_field)
Definitionget 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

Functionextract(quarter from timestamp_field)
Definitionget 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.


Let us know what you think about this document :)