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.

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.

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

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

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

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.

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

• datetimeAdd

Other tools

• SQL
• Spreadsheets
• Python

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

does the same thing as

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

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
    
                
Copy
    
    
                
Copied
    

is equivalent to the Metabase datetimeSubtract expression:

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

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)
    
                
Copy
    
    
                
Copied
    

produces the same result as

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

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.

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)
    
                
Copy
    
    
                
Copied
    

is equivalent to

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

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