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.

Syntax Example
datetimeSubtract(column, amount, unit) datetimeSubtract("2021-03-25", 1, "month")
Takes a timestamp or date value and subtracts the specified number of time units from it. 2021-02-25

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

amount:

  • A whole number or a decimal number.
  • 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:

Event Arrive By Depart At
Drinks November 12, 2022 6:30 PM November 12, 2022 6:00 PM
Dinner November 12, 2022 8:00 PM November 12, 2022 7:30 PM
Dancing November 13, 2022 12:00 AM November 12, 2022 11:30 PM

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

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

Comparing a date to a window of time

To check if an existing datetime falls between your start and end datetimes, use between.

Unfortunately, Metabase doesn’t currently support datetime functions like today. What if you want to check if today’s date falls between Arrive By and Depart At in our events example?

  1. Ask your database admin if there’s table in your database that stores datetimes 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 Events and the “Today” model on [Arrive By] <= [Today] and [Depart At] >= [Today].

The result should give you an Today column that’s non-empty for events that are happening while the night is still young:

Event Arrive By Depart At Today
Drinks November 12, 2022 6:30 PM November 12, 2022 6:00 PM November 12, 2022 12:00 AM
Dinner November 12, 2022 8:00 PM November 12, 2022 7:30 PM November 12, 2022 12:00 AM
Dancing November 13, 2022 12:00 AM November 12, 2022 11:30 PM  

Accepted data types

Data type Works with datetimeSubtract
String
Number
Timestamp
Boolean
JSON

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

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

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

Other tools

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

Read docs for other versions of Metabase.

Thanks for your feedback!

See something that needs fixing? Propose a change.