Skip to main content

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.


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

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



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

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


Functionextract(isodow from timestamp_field)
Definitionget the day of week number (0 - 6; Sunday is 0) (for timestamp values only)

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


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

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


Functionextract(doy from timestamp_field)
Definitionget the day of the year (1-365/366) (for timestamp values only)

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


Functionextract(week from timestamp_field)
Definitionget the number of the week of the year that the day is in

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


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.


Functionextract(month from timestamp_field)
Definitionget the number of the month within the year (1-12)

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


Functionextract(quarter from timestamp_field)
Definitionget the quarter of the year (1 - 4) that the day is in (for timestamp values only)

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.


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 :)