Using dates in table calculations (3.30+)

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:

f54c558e950ea94c2cc0924b8a04cacea6a9e399.png

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})

8af50712524c9c29d63d9837fee4f68afc1fc706.png

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

80e48be516448d457a920839abd413272c64650a.png

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

f2fc4d27bfddbca673a003071cba809c7a555d70.png

Creating an absolute date

Dates can be created using the date function:

216f682a52c57383d10b0fae6a94f0453b84d86c.png

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

bc1dbd49fcc504d293d6c6147f6c9e9f5c5a1cc1.png

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})

b80a1edb9e18310477f440404b2bac7293c0619d.png

Returning the current date

You can refer to now using the now function:

ac44005e44e861a87036b6ebed310add646bc73d.png

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())

d982b2432f83b0b048ffa8e6bf208111b6ad9f59.png

3 21 47.7K
21 REPLIES 21

brayden
Participant III

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.

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:
50962a1f01c035a948fd47a132867a122e0d109d.png

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 😕

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
Participant V

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…

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!

GenDemo
Participant V

Hi All
Is there an equivalent function to “datepart”? In otherwords I want to get the date part of a datetime variable, as the times are messing with my calculations.

Also, is there an alternative to the “now()” function that only gives the date?

The only thing I can think at the moment is to use " date(extract_year(now()), extract_month(now()), extract_date()) " but this seems overly cumbersome

For future people looking for this answer, it’s not too complex, but it’s also not super easy if you’re limited to table calculations.

  mod(diff_days(
    date(2020,1,6),
    date(
      extract_years(now()),
      extract_months(now()),
      extract_days(now()))), 7)

This in a calculated column will give you numerical date of the week with 0 being Monday.

(The first date given is the first Monday in 2020, it should work as long as that’s any date in the past, and you can change the 0 to whatever weekday you like by picking a similar date = if you wanted Wednesday as the ‘0’, you could use date(2020,05,06) for example.)

JohnRomanski
Participant III

Limit results based on latest date selected in filter
I had a use case where I had some looks in a dashboard where I wanted to show the most recent single week selected, from a filter that spanned multiple weeks. ( A bar chart tile, not limited would show the last 5 or 6 weeks in a trend. A pie chart tile would show the most recent week selected in that range.)

I am definitely looking for an easier way to do this, or any feedback. The easiest way I could figure out how to do it was to:

  1. Add two date fields to the explore activity_week_of_year and activity_year

  2. Provide a Yes/No value with a table calulcation:

Name: Is Latest Date?

${activity_week_of_year}=max(${activity_week_of_year}) 
AND
to_number(to_string(${activity_year}))=max(to_number(to_string(${activity_year})))
  1. Select Hide from Visualization for the two date fields.

  2. Select Hide No's from Visualization for the table calculation.

All of the results show up in the panel, but the pie chart displays based on the Yes values only.

Hope this helps someone else trying to compare dates inside of table calculations or get the maximum week value from a table calculation.

Pay attention to the nested use of to_number and to_string. to_number requires a string as an argument. I didn’t see it documented but to_string did convert the date to a string.

ngoushal
Participant I

It would be great if we can also extract quarter from the date. I see there is trunc_months but not trunc_quarter.

JohnRomanski
Participant III

Update, the calcuation above doesn’t account for year boundaries. You have to ensure that the highest week number (latest week) is in the highest year.

 ${sample.activity_week_of_year}=max(if(to_number(to_string(${sample.activity_year}))=max(to_number(to_string(${sample.activity_year}))),${sample.activity_week_of_year},0)) 
AND to_number(to_string(${sample.activity_year}))=max(to_number(to_string(${sample.activity_year})))

JohnRomanski
Participant III

@ngoushal Not sure what you mean, you can use the Quarter option for the date format from the explore and show it inline. You could also extract the quarter from the month number by subtracting 1, do a floor (divide by 3) then add 1. This will give you an integer which you could append the year and q on (“2020-Q” + 1).

floor((extract_months(${pdt_fw_operative.activity_date})-1) / 3)+1  

hope this helps 😀

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:

50962a1f01c035a948fd47a132867a122e0d109d.png

Do you guys know the opposite of this formula? I need to see stuff excluding the latest two weeks from today. More like extract_days(14, date) but the extract is just for a date. Please advise 

David_P1
Participant V

Hi @Simina,

You can actually input negative numbers in these date functions. This should give you 14 days before today:

add_days(-14, trunc_days(now()))

...which you can then use as a reference to consider only data before this date.

Good luck!

Hi David, thank you, this has worked! 

Appreciate it

mohiit_jain3
Participant III

can someone help me with how to find the difference between 2 dates? date_diff() or diff_days() is not working apparently in Looker somehow.

I am trying to find ${leaving_date}-${inserted_date}, these 2 are timestamps (yyyy-mm-dd 00:00:00) and lies in 2 different views in my looker. really appreciate it if someone could help me on this!

Whenever I create a table calculation or a custom dimension that should be in date format, it always comes out YYYY-MM-DD. Using trunc_months() or trunc_years() doesn’t not affect this: it still comes out YYYY-MM-DD.

An example of what I’m trying to do is trunc_months((add_months(1, ${account.created_month})))

This means that I can’t effectively merge off of these fields, if I’m looking to create a merge query based on monthly or yearly data.

I’d greatly appreciate any advice on how to get around this.

How can i show/create a date field in yyyy-mm format in looker as the date() function uses three inputs and null doesn’t work in this ???

Does anyone know how to extract the time from the “now()” formula so that I am only seeing the date and all calculations based on that field do not factor in hours and minutes? Ie every formula should act as if the time stamp is 12:00:00 AM. 

There is a lot of really useful information here - thank you!

Does anyone have a solution for comparing a DUE date with an ACTUAL date so when a task was DUE to when it ACTUALLY happened.. if I’ve recorded both dates, how can I report on how many times the task was actually completed on time?

Any help gratefully received :0)

We have date fields stored as strings.  Is there any support for date parsing?

​​​​​​​ - looker n00b

UPDATE: looks like there’s a `to_date()` function for this.

Top Labels in this Space
Top Solution Authors