University > Looker Studio Course > Formulas > Mastering the DATETIME_SUB Function in Looker Studio: A Guide to Manipulate Date and Time Information in Your Datasets

Mastering the DATETIME_SUB Function in Looker Studio: A Guide to Manipulate Date and Time Information in Your Datasets

In the plethora of functions available in Google Data Studio, DATETIME_SUB stands as a powerful yet straightforward function to wield in order to manipulate date and time information in your datasets. The primary function of DATETIME_SUB is to subtract a particular time interval from a specified date or even a date and time expression. This allows you to effectively cycle back in time from a given date or datetime field. This function, with its remarkable utility, becomes significant while performing operations such as historical comparisons.

Usage and Syntax

Utilizing DATETIME_SUB is as simple as understanding its clear syntax. It follows the pattern:

plaintext
DATETIME_SUB(datetime_expression, INTERVAL integer part)

The function requires two parameters for its application: datetime_expression and INTERVAL integer part. Here, the datetime_expression can be a Date or a Date & Time field or expression. The INTERVAL integer part consists of an integer representing the number of parts to subtract and the unit of time measurement to be subtracted.

Please note that the 'part' units include MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR — all providing you with a base for subtracting your preferred timeframe.

Examples

Illustrating the use of DATETIME_SUB becomes simpler with real-life scenarios. Given below are sales-related examples using DATETIME_SUB:

Suppose we want to compare this month's sales with that of exactly one month ago:

plaintext
DATETIME_SUB(current_date, INTERVAL 1 MONTH)

To review last quarter’s performance on products:

plaintext
DATETIME_SUB(Product_Release_Date, INTERVAL 1 QUARTER)

To calculate the time frame since the last promotion:

plaintext
DATETIME_SUB(Promotion_End_Date, INTERVAL 1 DAY)

Limitations

Though invaluable, DATETIME_SUB function has limitations. It does not support compatibility mode date types. Additionally, special handling is required when using MONTH, QUARTER, and YEAR parts at (or near) the last day of the month. The result will automatically be the last day of the new month if the resulting month has fewer days than the original date.

Tips

In using DATETIME_SUB function, always remember to specify your 'part' value in capitals as the function is case sensitive. And when dealing with date expressions, it's ideal to use the date_parse function to convert the string to a date or datetimes before utilizing DATETIME_SUB.

More function to use with Looker Studio

VARIANCE

Understanding the Variance Function in Looker Studio for Improved Business Strategies and Decision Making

MIN

Mastering the MIN Function in Looker Studio: A Comprehensive Guide on Data Analysis and Visualization Tools

COS

Looker Studio Studio's Magnificent Cosine Functionality

MAX

Mastering the Use of MAX Function in Looker Studio for Enhanced Business Data Analysis and Insights

DATETIME_SUB

Mastering the DATETIME_SUB Function in Looker Studio: A Guide to Manipulate Date and Time Information in Your Datasets

Become a Looker Studio expert
for free

Learn all the secrets of data analysis and create beautiful and effective dashboards thanks to our 30-second video courses! Join Catchr's community on YouTube.

Get more video lessons