DatetimeDiff

datetimeDiff gets the amount of time between two datetime values, using the specified unit of time. Note that the difference is calculated in whole units (see the example below).

Parameters

datetime1 and datetime2 can be:

• The name of a timestamp column,
• a custom expression that returns a datetime, or
• a string in the format "YYYY-MM-DD" or "YYYY-MM-DDTHH:MM:SS" (as shown in the example above).

unit can be any of:

• “year”
• “month”
• “day”
• “hour”
• “second”
• “millisecond”

Calculating age

Let’s say you’re a cheesemaker, and you want to keep track of your ripening process:

Mature Age (Months) is a custom column with the expression:

datetimeDiff([Aging Start], [Aging End], "month")

Calculating current age

Metabase doesn’t currently support datetime functions like today. To calculate the current age in our cheese example:

1. Ask your database admin if there’s table in your database that stores dates for reporting (sometimes called a date dimension table).
2. Create a new question using the date dimension table, with a filter for “Today”.
3. Turn the “Today” question into a model.
4. Create a left join between Cheese and the “Today” model on [Aging Start] <= [Today].

The result should give you a Today column that’s non-empty if today’s date is on or after the Aging Start date.

Then, you can calculate Current Age (Months) like this:

datetimeDiff([Aging Start], [Today], "month")

Accepted data types

We use “timestamp” and “datetime” to talk about any temporal data type that’s supported by Metabase.

If your timestamps are stored as strings or numbers in your database, an admin can cast them to timestamps from the Data Model page.

Limitations

datetimeDiff is currently unavailable for the following databases:

• Druid
• Google Analytics

Related functions

This section covers functions and formulas that work the same way as the Metabase datetimeDiff expression, with notes on how to choose the best option for your use case.

• SQL
• Spreadsheets
• Python

SQL

When you run a question using the query builder, Metabase will convert your graphical query settings (filters, summaries, etc.) into a query, and run that query against your database to get your results.

If our cheese sample data is stored in a PostgreSQL database:

SELECT DATE_PART('month', AGE(aging_end, aging_start)) AS mature_age_months
FROM cheese

is equivalent to the Metabase datetimeDiff expression:

datetimeDiff([Aging Start], [Aging End], "month")

Some databases, such as Snowflake and BigQuery, support functions like DATEDIFF or DATE_DIFF. For more info, check out our list of common SQL reference guides.

Spreadsheets

If our cheese sample data is in a spreadsheet where “Aging Start” is in column B and “Aging End” is in column C:

DATEDIF(B1, C1, "M")

produces the same result as

datetimeDiff([Aging Start], [Aging End], "month")

Yes, DATEDIF looks a bit wrong, but the spreadsheet function really is DATEDIF() with one “f”, not DATEDIFF().

Python

Assuming the cheese sample data is in a pandas dataframe column called df, you can subtract the dates directly and use numpy’s timedelta64 to convert the difference to months:

df['Mature Age (Months)'] = (df['Aging End'] - df['Aging Start']) / np.timedelta64(1, 'M')

is equivalent to

datetimeDiff([Aging Start], [Aging End], "month")

Further reading

• Custom expressions documentation
• Custom expressions tutorial
• Time series comparisons
• How to compare one time period to another
• Working with dates in SQL