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. Click the green + in the Pipeline panel and select Convert Type from the Transform group.
2. Choose your date column.
3. Set the target type to date.
4. Leave Auto-detect date format checked, which it is by default.

That checkbox is what does the real work. With it on, the app tries a long list of layouts before falling back to DuckDB's native cast: ISO (2026-03-14), US (03/14/2026), European (14/03/2026 and 14.03.2026), named months (14 Mar 2026, Mar 14, 2026), each with or without a time part. Two-digit years and AM/PM times are covered too.

The order matters: less ambiguous formats are tried first, so 03/04/2026 parses as March 4th, not April 3rd. If your file is genuinely DD/MM and you have days under 13 mixed in, type the exact pattern into Source date format (%d/%m/%Y) instead. That field overrides auto-detect and parses strictly. Either way, values that can't be parsed become NULL rather than erroring.

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. Click the green + in the Pipeline panel and 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. The default is the column name plus the part, so extracting month from order_date gives order_date_month.

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. Click the green + in the Pipeline panel and 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.

By default the shifted value is written back into the same column, and the column stays where it was in the grid. For a sales table of order_id, customer, order_date, amount, adding 30 days generates:

SELECT "order_id", "customer",
       "order_date" + INTERVAL '30 day' AS "order_date",
       "amount"
FROM "sales"

Every column is listed explicitly so the rewritten one lands in its original position instead of being appended at the end. Subtracting works the same way with a negative amount: "start_date" + INTERVAL '-1 year'.

Keeping the original: open Apply results into at the bottom of the panel and choose New Column. Name it due_date and you get the shifted date alongside the untouched original:

SELECT *, "order_date" + INTERVAL '30 day' AS "due_date"
FROM "sales"

No duplicate-the-column-first dance needed. The same Apply results into control shows up on Extract Date Part, Date Difference, Math, Update Values, and Extract Text.

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. Click the green + in the Pipeline panel and 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 →