DATETIME function: How to use & example

The DATETIME function is an essential tool in Looker Studio (formerly Google Data Studio) for working with dates and times. It allows you to create full timestamp values by combining separate components such as year, month, day, hour, minute, and second. This function is fundamental when you need precise control over time-based data, whether for building dynamic dashboards, calculating time intervals, or structuring reports.

What is the DATETIME function

The DATETIME function generates a combined date and time value from numeric inputs representing each component. The output is of the “Date & Time” data type, which ensures precision and consistency in your visualizations.

Syntax:

DATETIME(year, month, day, hour, minute, second)


Each argument must be numeric:

• year – the year value
• month – the month value (1–12)
• day – the day value (1–31)
• hour – the hour value (0–23)
• minute – the minute value (0–59)
• second – the second value (0–59)

The function merges all these components into one datetime stamp.

Building Datetimes from data fields

A common use of DATETIME is to merge multiple date and time columns from your data source into one full timestamp.
For example, if your data has separate columns for Year, Month, Day, Hour, and Minute, you can create a unified datetime field like this:

DATETIME(Year, Month, Day, Hour, Minute, 0)


This is useful when you need to visualize data chronologically or analyze trends over time. Having a single datetime field allows you to group and filter your data more effectively, and to create accurate time-based charts such as hourly or daily performance metrics.

Creating Fixed Datetimes from literal values

You can also create fixed points in time by directly specifying numeric values in the function. For instance:

DATETIME(2023, 2, 1, 12, 30, 0)


This returns February 1, 2023, at 12:30 PM. You can even mix static values and dynamic fields to set certain parts while taking others from your dataset:

DATETIME(2023, MONTH(Order_Date), DAY(Order_Date), 0, 0, 0)


This produces the start of each day in 2023 based on the month and day values from the Order_Date field.

Using DATETIME for dynamic date ranges

DATETIME can also be used to create dynamic date ranges that automatically adjust based on the current date. For example, this expression returns the start of the current week:

DATETIME(DATE_TRUNC(CURRENT_DATE(), WEEK), 1, 1, 0, 0, 0)


You can then build similar expressions to calculate the end of the week or other time frames. Combined with date functions such as CURRENT_DATE(), DATE_TRUNC(), and parameters like WEEK or MONTH, DATETIME allows you to build reports that automatically update over time.

Setting Start and End times

In many cases, you might need to define specific start and end times for your reports. You can use DATETIME to create these time frames:

DATETIME(YEAR(Date), MONTH(Date), DAY(Date), 9, 0, 0)


and

DATETIME(YEAR(Date), MONTH(Date), DAY(Date), 17, 0, 0)


These expressions represent 9:00 AM and 5:00 PM for a given date.
You can then use these timestamps with the DATETIME_DIFF() function to calculate time elapsed between two events, for example:

DATETIME_DIFF(Order_Delivery_Time, Order_Placement_Time, HOUR)


You can also combine DATETIME with DATETIME_ADD() to add specific time units, such as adding five days to a date.

Formatting Datetime values for reports

Once you’ve built a datetime field, you can format it for display in tables or charts using the FORMAT_DATETIME() function. For example:

FORMAT_DATETIME("%x", My_Datetime_Field)


This converts your datetime into a human-readable string using your preferred date format, making your reports easier to interpret.

Limitations and Best Practices

• DATETIME only accepts numeric values. Text, symbols, or non-numeric inputs are invalid.
• It does not automatically account for leap years or time zones.
• Always verify the order of your inputs: year, month, day, hour, minute, second.
• Combine DATETIME with date functions like DATE_TRUNC or CURRENT_DATE for dynamic reporting.
• Regularly check for missing or incorrect data that could affect your calculations.

Use Cases for DATETIME

• Combining date and time parts into a single timestamp
• Creating dynamic date filters such as “past 3 months” or “this week”
• Comparing time periods (week-over-week, year-over-year)
• Calculating elapsed time between two events
• Scheduling reports based on datetimes
• Tracking user activity or event sequences over time
• Building time-based cohorts or lifecycle analyses

Summary

The DATETIME function gives you precise control over how dates and times are represented and calculated in Looker Studio. It allows you to build and manipulate timestamps, create dynamic time ranges, and format them for clear visualization. Mastering DATETIME is key to producing accurate, flexible, and insightful time-based analyses in Looker Studio.

More Functions to explore in Looker Studio

• IF – Conditional logic for calculated fields
• DATE_TRUNC – Round dates to specific time periods
• DATETIME_DIFF – Calculate time elapsed between events
• FORMAT_DATETIME – Format dates and times for display
• MINUTE – Extract minutes from a datetime value