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