-
When we dive deep into the rich repertoire of functions that Google Data Studio offers, we find the UNIX_DATE function. This function is a powerful tool that converts an inputted date into the number of days passed since the epoch date, that is 1st January 1970. Thus, it becomes incredibly useful to convert time-sensitive data into numerical forms that make them amenable to different forms of analysis.
The Syntax
The UNIX_DATE function comes with a straightforward syntax:
UNIX_DATE(date_expression)
The
date_expression
in the syntax refers to the date or date & time field you wish to convert into UNIX date number. It's important to note, however, that this function dismisses time parts from the Date & Time field in its calculations.
How does the Function work?
The UNIX_DATE function operates by encoding the entered date into a format recognized by UNIX systems. While the conversion might seem quite intricate, for users, it’s seamless and quick to use. As an example, if your entered date is 2020-01-01, UNIX_DATE will calculate the number of days that have elapsed since 1st January 1970 (the epoch or starting date) and return those numbers.
Examples
Let's move to some practical examples to solidify our understanding of the function. We're going to modify Google's official example a bit and use sales-specific data.
Consider, you want to find out number of days since the start of UNIX epoch till the end of your store's clearance sale last year which happened on 2020-12-31, you’d type in the following:
UNIX_DATE(<2020-12-31>)
When processed, this will return
18627
, this count includes both the start and end dates.
Limitations of the Function
The UNIX_DATE function, as impressive as it is, comes with a few nuances. One limitation is that it only accepts Date or Date and Time fields. The function also ignores any time component from the field or the expression. Another important thing to observe is that this function isn't available for compatibility mode date types.
Tips for Using the Function
Having used the UNIX_DATE function over several projects, I have gathered a few insights that might be helpful to both beginners and intermediate-level users:
- It's key not to confuse the UNIX_DATE function with UNIX_TIMESTAMP, the former returns the count of days since epoch, but the latter returns the count of seconds.
- Also, while the UNIX_DATE returns a number that can seem abstract, it can be incredibly helpful for calculating differences, calculating averages, or finding out min/max of dates.
And there you have it, a comprehensive guide to using the UNIX_DATE function in your work with Google Data Studio. With a bit of practice, this function becomes instrumental in dealing with time-series data and performing calculations effortlessly.
