How to work with date in Data Studio?

Working with dates in Google Data Studio can be difficult.

In this blog post, we'll explain how to work with date in your Google Data Studio dashboard.

Data Studio allows you to use date selectors to visualize and compare dates and times using graphs and charts. This allows users to adjust the date range of the report. It is also possible to utilize date functions in calculated fields to alter or transform dates and times data.

The following calendar date and time data formats are supported by Data Studio:

How to change Google Data Studio date format

There are several methods for changing the date format.

1. First method of changing date format is doing it  in the data source:
   

2. Second method, it can be done in a specific component. As it can be seen on screenshot.

Date functions Google Data Studio

Calculated fields can be used to add new date and time columns to your data source or to report components directly.

Where to use date functions in Google Data Studio?

The DATE function, for example, can be used to create a whole date from discrete numeric day, month, and year values. You can also use functions like YEAR, DAY, and HOUR to extract date and time elements from a date. You can also use DATETIME_DIFF to calculate the difference between two dates, or DATETIME_ADD to add a specific amount of time parts.

Examples:

• To a date or a date and time, add a specified time interval. Let's suppose we have a date and time field that has some date and values.We would like to add five days to the dates and times.

DATETIME_ADD(datetime_expression, INTERVAL integer part)

• Represent today's date with Data Studio today function. This also works in Data Studio when you wish to get the current date. This function returns the supplied date as the current date. You can select a timezone; if none is specified, UTC is used as the default.

TODAY([time_zone]). [time_zone] represents the timezone.

• Calculate the difference between a date derived from data and a certain fixed date by using the following formula:

DATETIME_DIFF(date_field, DATE "2008-12-25", DAY)

Here's where you'll discover all of Google Data Studio's calculated field functions: link

Date & Time Compatibility Mode Explained

So what is date and time compatibility mode in Data Studio? Dimensions of time and date in data sources that were created prior to October 15, 2020, use the date & Time (compatibility mode) data type. Compatibility mode dates contain the format or example in the name as illustrated below:

It is easy to determine compatibility mode when the type of date field includes both format and type, e.g.If the date field's format has the format "Date (YYYYMMDD)", this is an compatibility mode date.

Why should upgrade a compatibility mode date field?

Compatibility mode dates will continue to function with your existing components as well as calculated fields. However, you aren't able to make use of compatibility mode dates for every one of the dates or time options. For those functions, you must modify your date fields into more modern Date as well as Date & Time data types.

How to upgrade compatibility mode date field in Google Data Studio :

1. Sign into Data Studio.
2. Edit your data source.
3. Locate the compatibility mode date field you want to convert.
4. To the right, click the Type menu, then select Date or Date & Time.
5. Select the desired date type.
6. In the dialog box that appears, click UPGRADE.

How to convert date format in Google Data Studio

The FORMAT_DATETIME function can be used to change the Date Format from the default  Aug 3, 2020 to 03.08.2020 . There are 2 methods to achieve this;

1. Change Type: Date

At the Data Source set the Semantic Type of the current Date field back to Date

2. Use FORMAT_DATETIME formula in data source.

Synax:

FORMAT_DATETIME("%d.%m.%Y", Date)

Here is a video to show in action:

Date and Date & Time literals

To utilize literal date and time values in a computed field, use the following marker before the value:

How to convert text to dates in Google Data Studio

When connecting to numerous sources of data, working with dates can be a tough undertaking. There is a myriad of date formats that can cause havoc when trying to create an analysis. If a format for dates isn't understood, you will end up with mistakes or null values and unfinished charts. Even the data is tagged with dates that are not correct.

With Google Data Studio, proper dates are necessary to create time-series charts that show comparisons with prior times, and use the control of date range. If you connect to a database, Data Studio will automatically try to find dates fields in the data source. If this does not work, however, you may manually input the format of the date by using built-in functions.

The most effective method to parse dates is to allow Data Studio do this itself. If this doesn't work due to the fact that your format for dates isn't recognized, you may go to the next options for manually defining the format.

1. Create a new source of data. Data Studio will automatically attempt to find dates fields (See the complete list of dates). If a type of date is recognized the field will display an icon of the date along with the type that was selected. It's recommended to check the type you have that you've chosen in your fields for the date in order to verify that they're correct.
   
2. If your date field displays as Text, you may choose manually the proper dates from the list. This could work in some instances. You can also go to the next option and change the text into an actual date within the form of a calculated field.

1. If the options above don't result in a satisfactory outcome, try the PARSE_DATE function within an calculated field to inform Data Studio how to interpret the date field in the following manner:

• PARSE_DATE(format_string, text), where the format_string must be composed of the date format symbols below and text is your date field.
• For example, if you have a field named Date Input with values formatted like 15/12/2020, you could use PARSE_DATE("%d/%m/%Y", Date Input).
• This is the list of compatible symbols for the most common date components that you can utilize to create an appropriate format string. See the PARSE_DATE documentation for a complete list of formats supported and their limitations.