Working with Dates: Extract, Calculate, and Transform

Dates are one of the trickiest parts of data analysis. Different formats, time zones, and the need to extract parts or calculate differences make date columns surprisingly complex. ExploreMyData provides three dedicated date operations that handle the common cases.

Prerequisite: column type

Date operations require a DATE or TIMESTAMP column. If your date column was imported as text (VARCHAR), convert it first:

1. Select Convert Type from the Transform group.
2. Choose your date column.
3. Set the target type to DATE or TIMESTAMP.

DuckDB's TRY_CAST() handles most common date formats automatically (ISO 8601, US format, European format). Values that can't be parsed become NULL.

1. Extract Date Part

Pull out a specific component from a date column (year, month, day, etc.) into a new column.

How to use:

1. Select Extract Date Part from the Date group.
2. Choose the date column.
3. Select the part to extract:
   • year: e.g., 2025
   • month: 1 through 12
   • day: 1 through 31
   • hour, minute, second: for TIMESTAMP columns
   • dow: day of week (0 = Sunday through 6 = Saturday)
   • week: ISO week number (1 through 53)
4. Optionally name the new column (defaults to column_part).

The generated SQL:

SELECT *, EXTRACT(month FROM "order_date")::INTEGER AS "order_date_month"
FROM "sales"

Common use case: Extract month and year to create a "year-month" grouping for time series analysis. Apply Extract Date Part twice (once for year, once for month), then use a custom SQL step to combine them.

2. Increment Date

Add or subtract a time interval from a date column. Useful for calculating deadlines, expiration dates, or shifting dates for analysis.

How to use:

1. Select Increment Date from the Date group.
2. Choose the date column.
3. Enter the amount (positive to add, negative to subtract).
4. Select the unit: day, week, month, year, hour, or minute.

The generated SQL:

-- Add 30 days to order_date
SELECT *, "order_date" + INTERVAL '30 day' AS "order_date"
FROM "sales"

-- Subtract 1 year
SELECT *, "start_date" + INTERVAL '-1 year' AS "start_date"
FROM "contracts"

Note: This operation replaces the original column's value. If you want to keep the original, copy the column first using Copy Column, then increment the copy.

3. Date Difference

Calculate the difference between two date columns. The result is a number (not a date) representing the gap in the unit you specify.

How to use:

1. Select Date Difference from the Date group.
2. Choose the first date column.
3. Choose the second date column.
4. Select the unit: day, week, month, year, hour, or minute.
5. Optionally name the result column (defaults to "date_diff").

The generated SQL:

SELECT *, DATEDIFF('day', "start_date", "end_date") AS "duration_days"
FROM "projects"

Important: The order matters. DATEDIFF('day', start, end) gives a positive number when end is after start, and negative when end is before start.

Practical examples

• Customer lifetime: Calculate the difference between first_purchase_date and last_purchase_date in days to measure customer retention.
• SLA compliance: Compare ticket_created_at and ticket_resolved_at to find tickets that took more than 24 hours.
• Cohort analysis: Extract the month from signup_date to group users into monthly cohorts, then calculate metrics per cohort.
• Fiscal quarters: Extract the month and use a SQL step with CASE WHEN to map months 1-3 to Q1, 4-6 to Q2, etc.

Try date operations on your data →