The DATETIME function is an indispensable tool for manipulating dates and times in Looker Studio. With DATETIME, you can construct full timestamp values from separate date and time components. This provides immense flexibility for transforming temporal data and gaining insights into trends over time. In this comprehensive guide, we’ll examine real-world examples of how to leverage DATETIME in calculated fields and visualizations like reports or dashboards.
What is DATETIME?
The DATETIME function creates a combined date and time value from numeric inputs representing each part. It is in fact a Looker Studio in-built functionality which translates the operating system/computer inner representation of dates into a human-readable one. The syntax is pretty straight forward:
DATETIME(year, month, day, hour, minute, second)
It merges the provided year, month, day, hour, minute and second values into a single datetime stamp with the Date & Time data type. You might ask yourself why I should use such numerical values since we human never deal with them. In fact, the component parts can come from distinct date dimension fields in your data source. Or you can specify literal numbers to create fixed points in time.
Use it to concatenate Date Parts into Datetimes
A very common use of DATETIME is to combine separate date and time columns from your data source into full datetime fields. For example, imagine your data has distinct Year, Month, Day, Hour and Minute columns. You could construct a complete timestamp like:
DATETIME(Year, Month, Day, Hour, Minute, 0)
This would merge the columns into a datetime value with 0 seconds.
You could then use this concatenated field to create chronological visualizations grouped by specific points in time rather than individual date parts. This unlocks more flexible time-based analysis. It can also provide more precise insights when timestamps are key components to your everyday work.
Easiest use case: construct Datetimes from Literals
Along with data source fields, you can specify literal values within DATETIME to construct fixed points in time:
DATETIME(2023, 2, 1, 12, 30, 0)
This returns the specific datetime of February 1, 2023 at 12:30 PM. You can also combine literals and fields to set certain date parts while pulling others from columns:
DATETIME(2023, MONTH(Order_Date), DAY(Order_Date), 0, 0, 0)
This would return the start of each day in the year 2023, using the month and day from the Order_Date column.
Let’s use it to build Dynamic Date Ranges
Values provided to DATETIME looker studio function can be fixed numerical values or variable like column values. They can be much more than that. In addition to blending columns, DATETIME can build dynamic date ranges based on parameters. For example this would return the start of the current week:
DATETIME(DATE_TRUNC(CURRENT_DATE(), WEEK), 1, 1, 0, 0, 0)
This can get the end of the week:
DATETIME(year, month, day, hour, minute, second)
Here we just added DATETIME_TRUNC which truncates a date to the specified granularity, CURRENT_DATE which returns the current date as of the specified or default timezone and WEEK which returns the week number for a given date. These are all date-related functions. By putting these DATETIME values in a date range filter, you can create week-over-week reports that update automatically based on the current date.
Set Start and End Times
A common reporting need is setting start and end times on dates, such as structuring a datetime as “9am to 5pm” on a given date.
You can leverage DATETIME to set the time frames. You set the start time with:
DATETIME(Year(Date), Month(Date), Day(Date), 9, 0, 0)
And then you set the end time with:
DATETIME(Year(Date), Month(Date), Day(Date), 17, 0, 0)
Now we can use it to perform calculations like the Time Elapsed between two timestamps. We only have to use DATETIME_DIFF which operates using our previously defined start and end times:
DATETIME_DIFF(Order_Delivery_Time, Order_Placement_Time, HOUR)
This returns the number of hours elapsed from the order being placed to it being delivered. You can also use DATETIME_ADD to increment datetimes by a certain number of time units, like adding 5 days to a date.
Converting Datetimes for use in Visualizations
Once you’ve constructed a datetime field, use the FORMAT_DATETIME function to output it as text in charts and table. Use the syntax to format the datetime in a localized MM/DD/YYYY style. Customize the format code based on your needs, ie the country format you want to use for your visualization:
FORMAT_DATETIME(%x, My_Datetime_Field)
Major use cases for DATETIME
With the power of DATETIME, here are just some of the use cases you can envision:
- Analyze metrics by time of day, day of week, month, etc
- Build date filters relative to current date like "past 3 months"
- Compare week-over-week or year-over-year time periods
- Calculate elapsed time between events like order and delivery
- Schedule reports based on datetimes
- Track user behavior trends over time
- Build cohorts of users by signup date
The possibilities are endless. DATETIME provides the core functionality for robust temporal analysis in Looker Studio.
Key takeaways
The DATETIME function enables you to fully manipulate and analyze dates and times within Looker Studio. By constructing dynamic datetimes from component parts, calculating with them, and formatting them, you can gain powerful insights into trends and sequences over time. When designing date-based calculations, focus on structuring them clearly and cohesively so they transform your data into meaningful views.
