DatetimeSubtract

datetimeSubtract takes a datetime value and subtracts some unit of time from it. You might want to use this function when working with time series data that’s marked by a “start” and an “end”, such as sessions or subscriptions data.

Parameters

column can be any of:

• 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”
• “day”
• “hour”
• “minute”
• “second”
• “millisecond”

amount:

• An integer. You cannot use fractional values. For example, you cannot subtract “half a year” (0.5).
• May be a negative number: datetimeSubtract("2021-03-25", -1, "month") will return 2021-04-25.

Calculating a start date

Let’s say you’re planning a fun night out. You know it takes 30 minutes to get from place to place, and you need to figure out what time you have to leave to get to each of your reservations:

Here, Depart At is a custom column with the expression:

datetimeSubtract([Arrive By], 30, "minute")

Checking if the current datetime is within an interval

Say you want to check if the current datetime falls between a start date and an end date. Assume the “current” datetime is November 12, 7:45 PM.

Depart At is a custom column with the expression:

datetimeSubtract([Arrive By], 30, "minute")

On My Way uses case to check if the current datetime (now) is between the datetimes in Arrive By and Depart At:

case(between(now, [Depart At], [Arrive By]), "Yes", "No")

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

If you’re using MongoDB, datetimeSubtract will only work on versions 5 and up.

Related functions

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

Metabase expressions

• datetimeAdd

Other tools

• SQL
• Spreadsheets
• Python

datetimeAdd

datetimeSubtract and datetimeAdd are interchangeable, since you can use a negative number for amount. We could use either expression for our events example, but you should try to avoid “double negatives” (such as subtracting a negative number).

datetimeAdd([Arrive By], -30, "minute")

does the same thing as

datetimeSubtract([Arrive By], 30, "minute")

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 events sample data is stored in a PostgreSQL database:

SELECT arrive_by - INTERVAL '30 minutes' AS depart_at
FROM events

is equivalent to the Metabase datetimeSubtract expression:

datetimeSubtract([Arrive By], 30, "minute")

Spreadsheets

Assuming the events sample data is in a spreadsheet where “Arrive By” is in column A with a datetime format, the spreadsheet function

A:A - 30/(60*24)

produces the same result as

datetimeSubtract([Arrive By], 30, "minute")

Most spreadsheets require you to use different calculations for different time units (for example, you’d need to use a different calculation to subtract “days” from a date). datetimeSubtract makes it easy for you to convert all of those functions to a single consistent syntax.

Python

If our events sample data is in a pandas dataframe column called df, you can import the datetime module and use the timedelta function:

df['Depart At'] = df['Arrive By'] - datetime.timedelta(minutes=30)

is equivalent to

datetimeSubtract([Arrive By], 30, "minute")

Further reading

• Custom expressions documentation
• Custom expressions tutorial
• Time series analysis