Datetime Aggregation
Extract year, quarter, month, week, day, or time components from a datetime column into a new column. Configure multiple extractions in one operation.
Datetime Aggregation extracts one component from a datetime column and stores it in a new column. Choose from 11 components: year, quarter, month, week number, weekday, date, day, hour, minute, and second. The source column is unchanged. You can configure multiple extractions in one operation, each targeting any datetime column.
When to Use Datetime Aggregation
- Monthly trend analysis. Extract
monthfromorder_dateto group orders by month name in a Group By. - Day-of-week patterns. Extract
weekdayfromorder_dateto see which days drive the most revenue. - Quarterly reporting. Extract
quarterfromorder_dateto produce Q1/Q2/Q3/Q4 labels for pivot tables. - Hour-of-day analysis. Extract
hourfrom a timestamp to identify peak activity windows. - Date-only column. Extract
datefrom a full timestamp to strip the time component for date-level joins.
Sample Dataset
The examples in this doc use the Edilitics sample orders dataset. Download it to follow along in your own workspace.
edilitics_sample_orders.csv
Sample orders dataset for hands-on practice · 500 rows
Relevant columns for Datetime Aggregation examples:
Prop
Type
Extract Options
All 11 extract options, their output type, and example output for 2024-03-15 14:32:07:
| Label | Extracts | Output type | Example |
|---|---|---|---|
| Year | 4-digit year | Integer | 2024 |
| Quarter | Calendar quarter (1-4) | Integer | 1 |
| Month | Full month name | String | "March" |
| Week Number | ISO week number (1-53) | Integer | 11 |
| Week Day | Full weekday name | String | "Friday" |
| Date | Calendar date only | Date | 2024-03-15 |
| Day | Day of month (1-31) | Integer | 15 |
| Time | Time portion only | Time | 14:32:07 |
| Hour | Hour (0-23) | Integer | 14 |
| Minute | Minute (0-59) | Integer | 32 |
| Second | Second (0-59) | Integer | 7 |
Month returns the full month name as a string ("January", "February", ...). Week Day returns the full day name ("Monday", "Tuesday", ...). If you need numeric month or weekday for sorting, use Cast Data Type after extraction or use the Code Editor.
How to Apply Datetime Aggregation
Open the Datetime Aggregation operation
In your Transform pipeline, click Add Operation and select Datetime Aggregation from the operation list.
If no datetime columns exist in the dataset, an info toast appears and the operation closes automatically. Cast string date columns to datetime first using Cast Data Types.
Select the datetime column
In the Datetime Column dropdown, choose the source column. Only datetime, timestamp, date, and time columns appear.
In the sample dataset, order_date and delivery_date are both available.
Choose what to extract
In the Extract dropdown (enabled after a column is selected), choose one of the 11 extract options. The Output Column Name field is auto-populated with a name in the format year_order_date, month_order_date etc.
Edit the output name if needed. The name must:
- Contain only letters, numbers, and underscores
- Not start with
__or a number - Not already exist in the dataset or in another row in this operation
Add more extractions (optional)
Click Add Operation to add another row. This button is disabled until the current row is fully complete. Use this to extract multiple components in one save - for example, year, month, and weekday from order_date in a single operation.
Click Save & Preview
Click Save & Preview. Edilitics applies all extractions and adds the new columns to the dataset. The success toast confirms: "Datetime component extracted. Preview updated with the new column."
Verify in the preview
Check the new columns. Confirm month names and weekday names are correct. If a row has a null in the source datetime column, the extracted column will also be null for that row.
Before and After
Two extractions from order_date: month and weekday.
Input:
| order_id | order_date | revenue |
|---|---|---|
| ORD-2024-0001 | 2024-01-15 08:32:00 | 12450.00 |
| ORD-2024-0047 | 2024-03-22 14:15:00 | 8320.50 |
| ORD-2024-0099 | 2024-06-07 09:45:00 | 83841.00 |
Output:
| order_id | order_date | revenue | month_order_date | weekday_order_date |
|---|---|---|---|---|
| ORD-2024-0001 | 2024-01-15 08:32:00 | 12450.00 | January | Monday |
| ORD-2024-0047 | 2024-03-22 14:15:00 | 8320.50 | March | Friday |
| ORD-2024-0099 | 2024-06-07 09:45:00 | 83841.00 | June | Friday |
Source column unchanged. Two new columns added.
Code Equivalent
-- Compatible with PostgreSQL, BigQuery, Snowflake, Redshift, DuckDB
SELECT
*,
TO_CHAR(order_date, 'Month') AS month_order_date,
TO_CHAR(order_date, 'Day') AS weekday_order_date,
EXTRACT(YEAR FROM order_date) AS year_order_date,
EXTRACT(QUARTER FROM order_date) AS quarter_order_date
FROM orders;import polars as pl
df = df.with_columns([
pl.col("order_date").dt.month().alias("month_num_order_date"),
pl.col("order_date").dt.strftime("%B").alias("month_order_date"),
pl.col("order_date").dt.strftime("%A").alias("weekday_order_date"),
pl.col("order_date").dt.year().alias("year_order_date"),
pl.col("order_date").dt.quarter().alias("quarter_order_date"),
])After Save & Preview, the pipeline shows a DQ delta badge on this step - green if the table score improved, red if it dropped. See Data Quality Scoring for how scores are calculated.
After Save & Preview, the pipeline shows a DQ delta badge on this step - green if the table score improved, red if it dropped. See Data Quality Scoring for how scores are calculated.
Operation Reference
Prop
Type
Frequently Asked Questions
Next Steps
Group By
Group by the extracted month, quarter, or weekday column to produce time-based summaries.
Datetime Delta
Calculate the number of days, hours, or minutes between two datetime columns.
Manage Timezones
Convert datetime columns to a different timezone before extracting components.
Cast Data Types
Cast string date columns to datetime so they appear in the Datetime Column dropdown.
Need help? Email support@edilitics.com with your workspace, job ID, and context. We reply within one business day.
Last updated on
Datetime Delta
Calculate the time difference between two datetime columns in seconds, minutes, hours, or days. Configure multiple delta calculations in a single operation.
Manage Timezones
Convert datetime columns to a target IANA timezone. All datetime columns are pre-loaded. Apply one timezone to all selected columns without writing code.