The problem: You have an Order Data Model. This model has a Datetime dimension created_at that contains timestamps like 2022-10-22 09:41:31.
What is the best way to get derived fields from those timestamps, such as extracting only the Hour part or determining what day of the week from the Date portion, etc.?
Answer:
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.
The recommended way would be to create a custom dimension. Let’s begin by making a dimension to extract hour from the created_at timestamp.
Step 1: Go to Data Modeling > select your Model > 
Add Field.
In this example, I am using ecommerce_orders model.
Step 2: In the Create Custom Dimension prompt, create our new created_at_hour field.
The formula for this field is:
extract(hour from {{ #THIS.created_at }})
Step 3: Verify if this field works correctly.
You can try creating a new visualization using the newly created field. Below is a simple table that lists out the timestamps and their corresponding extracted hours.
Similarly, you can create dimensions to extract other timestamp parts. Here is the formula for popular ones:
Extract day number (with 0 being Sunday):
extract(isodow from {{ #THIS.order_created_at }}) - 1
Extract day of the week:
to_char({{ #THIS.order_created_at }}, 'Day')
