University > Looker Studio Course > Formulas > Using the UNIX_DATE Function in Looker Studio: A Comprehensive Guide to Converting Time-Sensitive Data into Numerical Form

Using the UNIX_DATE Function in Looker Studio: A Comprehensive Guide to Converting Time-Sensitive Data into Numerical Form

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

, 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.

More function to use with Looker Studio

CONTAINS_TEXT

Introduction to the CONTAINS_TEXT Function

COUNT

Mastering the COUNT Function in Looker Studio

CASE WHEN

Data Transformation with CASE WHEN in Looker Studio

DATE

Exploring the DATETIME Function in Looker Studio: Syntax, Usage, Limitations, and Tips for Accurate Data Visualization

HOUR

Exploring the HOUR Function in Looker Studio: An In-Depth Guide to Understanding and Implementing This Core Feature in Time Series Analysis

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