Note: Google Data Studio is now known as Looker Studio.
When you create or edit a data source schema in Looker Studio, you get the option to select the data type of the data source schema field.
One of the data types supported by Looker Studio is: ‘Date & Time’:
As you can see from the screenshot, Looker Studio supports several different types of dates and times.
The data types for ‘date’ fields
The data types for ‘date’ fields can be divided into two categories:
#1 Absolute dates – Refers to a specific date you can point to on a calendar. For example:
2022
Quarter 2, 2022
Oct 2022
Oct 2, 2022.
#2 Relative dates – It refers to a date that you can not point to on a calendar. For example:
Quarter 2
October
Mon
The data types for ‘time’ fields
The data types for ‘time’ fields can be divided into two categories:
#1 Absolute time – Refers to a specific time accompanied by an absolute date. For example:
Sept 10, 2022 10 PM
Sept 10, 2022, 10:30 PM
Sept 10, 2022, 10:30:14 PM
#2 Relative time – It refers to a specific time that is not accompanied by an absolute date. For example:
1 am
3 am
7 pm
Absolute dates
An absolute date is a date that you can point to on a calendar.
Following are examples of absolute dates:
2022
Q1 2022
Nov 2022
Nov 10, 2022
Absolute date types
These are the data types meant for absolute dates.
The following are examples of absolute date types in Looker Studio:
Date
Year
Year Quarter
Year Month
ISO Year Week
The best practice is to always use the full date in your data source for all the fields which use absolute date types and then adjust the field type in your data source schema.
For example, consider the following Google Sheets data source schema:
Here we are using the full date in our data source for all the fields which use absolute date types.
If we create a data source schema from this data source, it will look something like the one below:
Let’s adjust the field types of various date fields:
Now, if we create a report from this data source schema, it would look something like the one below:
As you can see from the report above, a date field’s type is not always the same as its display format.
The visual appearance of the actual date in the report depends upon the following two factors:
Here the field’ order date’ contain dates in the format: YYYY-MM
So when setting up the data source schema, we should set the data type of ‘Order Date’ field to ‘Year Month’:
But what if we set the data type of the ‘Order Date’ field to Date:
The data type ‘Date‘ tells Looker Studio to expect year, month and day in the ‘order date’ fields in the underlying data source.
Since the day information is missing in our data source, what looker studio will do is, add the default day ‘1’ to your report.
So when you create a report from the data source schema, it would look like the one below:
But this report doesn’t match our data source:
The ‘Order Date’ 2022-01 in our data source denotes ‘Jan 2022’ and not ‘Jan 1, 2022’.
The ‘Order Date’ 2022-02 in our data source denotes ‘Feb 2022’ and not ‘Feb 1, 2022’.
.
.
The ‘Order Date’ 2022-11 in our data source denotes ‘Nov2019’ and not ‘Nov 1, 2022’.
That’s why using the correct absolute date type (the one that matches your data source) is very important.
Absolute time types
An absolute time is a specific time accompanied by an absolute date.
Following are examples of absolute time:
Sept 10, 2022 10 PM
Sept 10, 2022 10:30 PM
Sept 10, 2022 10:30:14 PM
Sept 10, 2022 22:30:14
Data types for absolute time
These are the data types meant for absolute times.
The following are the data types for absolute times in Looker Studio:
Date & Time
Date Hour
Date Hour Minute
The best practice is to always use the full date and time in your data source for all the fields which use absolute time type and then adjust the field type in your data source schema.
For example, consider the following Google Sheets data source:
Here we are using the full date and time in our data source for all the fields which use absolute time types.
If we create a data source schema from this data source, it will look something like the one below:
Let’s adjust the field types of various time fields:
Now, if we create a report from this data source schema, it would look something like the one below:
Changing a field’s type from absolute date to absolute time
You should change a field’s type from absolute date to absolute time only when the time information is available in your data source. Otherwise, you will get incorrect/unexpected time data in your report.
For example, consider the following Google Sheets data source:
Here the ‘order date’ field does not contain any time data.
Now let’s set the data type of the ‘Order Date’ field from ‘Date’ to ‘Date & Time‘:
The data type ‘Date & Time‘ tells Looker Studio to expect both date and time data in the ‘order date’ field in our data source.
Since the time information is missing in our data source, Looker Studio will add the default hour ’00’, default minute ‘0’, and default second ‘0’ to all ‘order date’ fields to our report.
The default time that looker studio would append to the dates in the ‘Order Date’ field would be 00:00:00
So when you create a report from the data source schema, it would look like the one below:
That’s why it is important that you change a field’s type from absolute date to absolute time only when the time information is available in your data source.
Relative dates
A relative date is a date you cannot point to on a calendar.
Following are examples of relative dates:
Q1, Q2, Q3, Q4 – Here, we don’t know which year is being referred to.
Jan, Feb, March, April, etc. – Here, we don’t know which year is being referred to.
Mon, Tue, Wed, Thu, etc.- Here, we don’t know which week, month or year is being referred to.
Relative date types
These are the data types meant for relative dates.
The following are examples of relative date types in Looker Studio:
Quarter
Month
ISO Week
Month Day
Day of Week
Day of Month
The best practice is to always use the full date in your data source for all the fields which use relative date types and then adjust the field type in your data source schema.
For example, consider the following Google Sheets data source:
Here we are using the full date in our data source for all the fields which use relative date types.
If we create a data source schema from this data source, it will look something like the one below:
Let’s adjust the field types of various date fields:
Now, if we create a report from this data source schema, it would look something like the one below:
As you can see from the report above, a date field’s type is not always the same as its display format.
Relative time types
Relative time is the time that is not accompanied by an absolute date.
Following are examples of relative time:
1 am, 3 am, 7 pm, etc. – Here, we don’t know which day, month or year the time occurred.
These are the data types meant for relative times.
Following are data types for a relative time in Looker Studio:
Hour
Minute
The best practice is to always use the full date and time in your data source for all the fields which use relative time types and then adjust the field type in your data source schema.
For example, consider the following Google Sheets data source:
Here we are using the full date and time in our data source for all the fields which use relative time types.
If we create a data source schema from this data source, it will look something like the one below:
Let’s adjust the field types of various time fields:
Now, if we create a report from this data source schema, it would look something like the one below:
Best practices for changing the date and time fields
Google recommends the following best practices for changing the date and time fields in Looker Studio:
Best practice #1: Avoid changing a relative date field to an absolute date field
That’s because the new field won’t have sufficient information to function properly as a complete date.
Best practice #2: Avoid changing a relative time field to an absolute time field
That’s because the new field won’t have sufficient information to function properly as a complete time.
Best Practice #3: Avoid changing a date field type to an incompatible data type
You should only change the date field type to another type that is compatible with the corresponding data in your data source. Changing to an incompatible data type can cause an error(s) in your reports.
Best Practice #4: Avoid changing a time field type to an incompatible data type
You should only change the time field type to another type that is compatible with the corresponding data in your data source. Changing to an incompatible data type can cause an error(s) in your reports.
Best practice #5: Avoid changing date/time type at the individual chart level
You can change the date/time type both at the data source schema level and at the individual chart level (via the ‘edit’ mode of your report).
However, you should only change the date/time type at the data source schema level.
When you change the date/time type at the data source schema level, you change it globally for every report (attached to your data source schema) and for every chart in your report that uses that date/time field.
When you change the date/time type only for a specific chart (like ‘table’) in your report, you could create data discrepancies as the rest of the charts in your report would still use the date/time types set at the data source schema level.
Best practice #6: Change date/time types by making a copy of the existing field
Google recommends that if you want to change the date/time types at the data source schema level, then the best way to do that is by making a copy of the existing date/time field via the data source schema editor.
Follow the steps below to do that:
Step-1: Navigate to your data source schema editor and then click on the three dots next to your date/time field:
When you click on the three dots, you are going to see a drop-down menu like the one below:
Step-2: Click on ‘Duplicate’:
You should now see the duplicate of the ‘Order Date & Time’ field in your data source schema as ‘copy of Order Date & Time’:
Step-3: Rename your data source schema field by double-clicking on the field name:
And then entering the new name:
Step-4: Press the enter key to confirm the new field name. Once you press the enter key, the field name will appear as a green chip:
Step-6: Change the data type of your new field by selecting a new date/time type from the drop-down menu:
Step-7: Now can safely use this new field in your report:
Frequently Asked Questions About Looker Studio Date Format and Time Explained
What is the difference between absolute dates and relative dates?
Absolute dates refer to a specific date that you can point to on a calendar. For example: 2020, Quarter 2 2020, Oct 2020, Oct 2, 2020.
Relative dates refer to a date you cannot point to on a calendar. For example: Quarter 2, October, Mon.
What is the difference between absolute dates and relative times?
Absolute times refer to a specific time accompanied by an absolute date. For example: Sept 10, 2020 10pm, Sept 10, 2020, 10:30pm, Sept 10, 2020, 10:30:14pm.
Relative time refers to a specific time that is not accompanied by an absolute date. For example: 1am, 3am, 7pm.
"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."
Here’s what we’re going to cover in this training…
#1 Why digital analytics is the key to online business success.
#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.
#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.
#4 Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.
#5 The number 1 reason why conversion optimization is not working for your business.
#6 How to advertise on any marketing platform for FREE with an unlimited budget.
#7 How to learn and master digital analytics and conversion optimization in record time.
My best selling books on Digital Analytics and Conversion Optimization
Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.
Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.
Attribution Modelling in Google Analytics and BeyondSECOND EDITION OUT NOW!
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.
Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.
About the Author
Himanshu Sharma
Founder, OptimizeSmart.com
Over 15 years of experience in digital analytics and marketing
Author of four best-selling books on digital analytics and conversion optimization
Nominated for Digital Analytics Association Awards for Excellence
Runs one of the most popular blogs in the world on digital analytics
Consultant to countless small and big businesses over the decade
Get the FREE ebook on Looker Studio (formerly Google Data Studio) (75 pages)
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.