Looker Studio Function : DATETIME_SUB
Category: DATE Function
The DATETIME_SUB function in Looker Studio subtracts a specified time interval from a given date or datetime value. It’s used to analyze past data by shifting dates backward, making it ideal for comparisons, historical trends, cohort analysis, and time-based reporting in dashboards and reports.
Purpose of the DATETIME_SUB Function
1. Data Distribution Analysis :
Use DATETIME_SUB to shift time windows (e.g. subtract days/weeks) and explore how metrics distribute across past periods, spotting trends like load spikes or seasonality.
2. Customer Segmentation :
Subtract specific intervals to define cohorts—such as customers active in the past quarter—enabling side-by-side comparisons and targeted segment analysis.
3. Decision Making :
By analyzing prior-period performance through subtraction, teams gain confidence in trend changes, supporting data-driven decisions across marketing, product, or sales initiatives.
Type of Calculation & Practical Use Cases
✅ Performance Benchmarking
Compare current metrics with a past period (e.g. subtract 1 week) to track improvement or decline.
✅ Customer Segmentation
Use DATETIME_SUB in cohort definitions to group users by signup date or last purchase relative to now.
✅ Time Series Analysis
Build rolling time windows (e.g. last 30 days via subtracting 30 days from current date) to generate trend charts.
DATETIME_SUB(datetime_expression, INTERVAL integer_value date_part)
| Parameter | Type | Description |
|---|---|---|
datetime_expression | DATETIME | The original datetime value to modify. |
int64_expression | INTEGER | The number of units to add. |
date_part | STRING | The type of time unit (e.g., DAY, WEEK, MONTH, YEAR). |
How Does the DATETIME_SUB Function Work?
DATETIME_SUB subtracts the specified interval from a date, returning a new date or datetime. Use it when you need to shift analysis into the past—for example, comparing today’s data to last week’s or generating month-over-month reports.
Example of DATETIME_SUB with Result
DATETIME_SUB(DATETIME “2025-06-19 14:30:00”, INTERVAL 7 DAY)
Result : 2025-06-12 14:30:00
Example : 1 Basic Usage
DATETIME_SUB(DATETIME “2025-06-19 12:00:00”, INTERVAL 2 DAY)
→ 2025-06-17 12:00:00
Example 2: Column Usage
DATETIME_SUB(Order_Date, INTERVAL 1 MONTH)
Returns each order’s date minus one month.
Example 3: Handling Decimals and Scientific Notation
DATETIME_SUB only accepts whole integers. If your data has decimals (e.g. 1.5 days), wrap it in FLOOR() to round down, or combine with other numeric functions to normalize before subtracting.
Example 4: Combining with Other Functions
Adjusting time zones:
DATETIME_SUB([TimeField], INTERVAL 5 HOUR)Rolling windows:
DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 30 DAY)Dynamic cohorts:
DATETIME_DIFF(CURRENT_DATETIME(), DATETIME_SUB(Signup_Date, INTERVAL 1 YEAR), DAY)
Tips and Tricks
Always use uppercase for
INTERVALunits. reddit.com+11catchr.io+11support.google.com+11Be mindful when subtracting months on months-end—result auto-adjusts to the last valid date.
Cast to date if working purely with dates:
CAST(DATETIME_SUB(... ) AS DATE)
The DATETIME_SUB function is used to subtract time intervals from a datetime field, enabling users to analyze historical data, compare past performance, and create dynamic time-based filters for reports.
Yes, DATETIME_SUB supports various time units like DAY, WEEK, MONTH, QUARTER, and YEAR, allowing you to perform flexible time-based calculations and compare metrics across different periods or create rolling time windows.
Looker Studio automatically adjusts invalid dates to the nearest valid one. If you subtract and land on February 29 in a non-leap year, it defaults to February 28 to maintain a valid result.
No, DATETIME_SUB only accepts whole integer values for intervals. If you have decimal values, you should round them using functions like FLOOR() or ROUND() before applying the subtraction.
Use DATETIME_SUB when you need to analyze or compare historical data by shifting backward in time. DATETIME_ADD is used to project forward, such as forecasting or planning future dates.