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
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.