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”
• “quarter”
• “month”
• “week”
• “day”
• “hour”
• “minute”
• “second”

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")

To calculate the current age of a cheese in months, you use now as the second datetime parameter, like this:

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

To calculate the current age of a cheese in days, you’d use:

datetimeDiff([Aging Start], now, "day")

Accepted data types

We use “timestamp” and “datetime” to talk about any temporal data type that’s supported by Metabase. For more info about these data types in Metabase, see Timezones.

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

Limitations

datetimeDiff is currently unavailable for the following databases:

• Druid

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 analysis