Using dates in table calculations (3.30+)

done
low_priority
reply

#1

As of Looker 3.30, it is possible to perform date operations in table calculations. This includes subtracting dates, extracting dateparts from dates, creating an absolute date, and returning the current date.

Subtracting dates

Dates can be subtracting by using of the functions starting with diff_. You can subtract the number of seconds, minutes, hours, days, months, or years between the two dates using one of the following functions:

diff_days
diff_hours
diff_minutes
diff_months
diff_seconds
diff_years

Each follows this pattern:

For example, this can be used to determine number of days between User Created Date and First Order Date like so:

diff_days(${users.created_date}, ${users_orders_facts.first_order_date})

Extracting portions of dates

Functions starting with extract_ can be used to extract a particular datepart from a date. You can extract the seconds, minutes, hours, day, month, or year of a date using one of the following functions:

extract_days
extract_hours
extract_minutes
extract_months
extract_seconds
extract_years

For example, this could be used to highlight only orders that occurred on the first of the month like so:

extract_days(${orders.created_time}) = 1

Creating an absolute date

Dates can be created using the date function:

And datetimes/timestamps can be created using the date_time function:

This enables you to compare a date in your database to any particular date you have chosen. For example, if you wanted to see how many minutes each order on a particular day occurred after a sale was announced.

diff_minutes(date_time(2015,09,17,9,00,00),${orders.created_time})

Returning the current date

You can refer to now using the now function:

This is particularly useful for calculating how long ago a particular date was. For example, if you have a First Order Date field, you can use diff_date and now to determine how many days it’s been since a users’ first order date

diff_days(${users_orders_facts.first_order_date}, now())


Is there a way to do date offset using Calculations?
Looker 3.30 Release Notes
[Retired] Calculating the difference between dates
(Brayden) #2

It would be great if there were a date_add(date, number) function [or just build this into the functionality of “+”], since I haven’t been able to find a way to construct a calculated column of dates that is offset from an existing date dimension.

The closest I’ve found is
date(extract_years($date}),extract_months(${date}),extract_days(${date}) + 1)
but this doesn’t handle transitions at the ends of months or years.


(sam) #3

Hi @brayden,

This feature is now available as of Looker 3.48! You can see here we added a whole slew of date adding functions:


(Chris) #4

Hi,
My date field in the datasource is in yyyy-mm-dd dformat. When trying to compare it to now() I get error of mismatching data types. Is there any simple way to remove time part from now() or to add time to my date field? Otherwise I will try working with
DATE(TRUNC_YEARS(now()),TRUNC_MONTHS(now()),TRUNC_DAYS(now()))
which doesn’t seem right :confused:


(jon.allen) #5

Hi Chris,

Thanks for reaching out. The correct method will depend on which dialect your database is using, but based on the alternative expression you’ve provided, it looks like DATE(NOW()) should provide the same results as DATE(TRUNC_YEARS(now()),TRUNC_MONTHS(now()),TRUNC_DAYS(now())) . If you’re working with MySQL, CURDATE() and CURRENT_DATE() provide identical functionality.

To answer your question about adding time to a date field – there are functions that can be used to convert date to timestamp (i.e. adding a time to a date expression) but this would very likely lead to the comparison evaluating as false, if your datasource’s data does not match the time exactly.

Cheers,
Jon


(Dawid) #6

What if I need to extract day of the week or week of the year? To create calculations like week on week comparison but only up to the same point last week…


(Izzy) #7

There are some super complex ways to calculate day of week in a table calculation, but it’s almost always a better experience if you reference a dimension_group instead, since you can add a timeframe for day_of_week, week_of_year, etc. See here: https://docs.looker.com/reference/field-params/dimension_group#timeframe_options

If you’re married to a table calc for this use case, I can try and rustle up the calculation to get day of week. Let me know!