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:
|Date||Sep 15, 2020|
|Date & Time||Sep 15, 2020, 6:10:59:59 PM|
|Year Quarter||Q3 2020|
|Year Month||Sep 2020|
|ISO Year Week||Sep 14, 2020 to Sep 20, 2020 (Week 38)|
|Date Hour||Sep 15, 2020, 6 PM|
|Date Hour Minute||Sep 15, 2020, 6:10 PM|
|ISO Week||Week 35|
|Month Day||Sep 15|
|Day of Week||Tuesday|
|Day of Month||23|
How to change Google Data Studio date format
There are several methods for changing the date format.
- 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?
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
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.
- 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 :
- Sign into Data Studio.
- Edit your data source.
- Locate the compatibility mode date field you want to convert.
- To the right, click the Type menu, then select Date or Date & Time.
- Select the desired date type.
- In the dialog box that appears, click UPGRADE.
How to convert date format in Google Data Studio
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;
- Change Type: Date
At the Data Source set the Semantic Type of the current Date field back to
2. Use FORMAT_DATETIME formula in data source.
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.
- 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.
- 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.
- 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_stringmust be composed of the date format symbols below and
textis your date field.
- For example, if you have a field named
Date Inputwith 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.
|Date part||Example string||Symbol|
|Full month name||January||%B|
|Abbreviated month name||Jan||%b|
|Day of the month as a number (01-31)||01||%d|
|Month as a number (01-12)||01||%m|
|Abbreviated year (00-99)||21||%y|