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,…).
![](https://cdn.holistics.io/docs/datetime/group_by_day_of_week.jpg)
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
![](https://cdn.holistics.io/docs/datetime/extract-subfields.png)
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 |
![hour_of_day](https://cdn.holistics.io/docs/datetime/hour_of_day.png)
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’ |
![day_of_week](https://cdn.holistics.io/docs/datetime/day_of_week.png)
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:
![](https://cdn.holistics.io/docs/datetime/group_by_day_of_week.png)
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.