Hi. How can we help?

Search

Date functions

Analytics provides many functions that perform different mathematical, logical, and table-based calculations. You can find a more in-depth catalog of available functions on the Looker functions and operators page.

To make it easier, these are categorized in five different ways:

This article covers seven of the Date based functions.

Add_Days (or Weeks, Months, Years)

This looks at a date field and then changes it by a defined number (one day is equivalent to the number 1).

  • The first argument is the number difference, which can be positive or negative.
  • The second argument is the date that you wish to adjust.

In this example, there are two calculations — one to change the sales day forward by one day, the second to change the sales day backward by one day:

table_calculations.png

The calculations are as follows:

  • add_days(1,${sales.time_stamp_date})
  • add_days(-1,${sales.time_stamp_date})

If built properly, here are the results:

10_calculations.png

Date

This creates a date out of provided numbers, which requires three arguments:

  • The first argument is the number for the year.
  • The second argument is the number of the month (the number one corresponding to January, the number two corresponding to February, and so on).
  • The third argument is the number of the day.

Example date calculation:

edit_table_calculation.png

Example result:

calculation_results.png

Diff_Days (or Weeks, Months, Years)

This counts the difference in days (or other denominations) between two dates.

To prepare it, the first argument is the start date (or earlier date), and the second is the end date (or later date).

Note: This logic is different from how spreadsheets may calculate differences between dates, where you might subtract a smaller from a larger to yield a positive number.

If you want to count the difference from the start of the month to the current sales date, prepare a calculation like this:

add_table_calculation.png

The calculation:

  • diff_days(${sales.time_stamp_month},${sales.time_stamp_date})

And if done properly, here is the result:

correct_calculation.png

Extract_days (or Months, Years)

This converts part of a date to a non-date number. 

Example

new_calculation.png

The calculations:

  • extract_days(${sales.time_stamp_date})
  • extract_months(${sales.time_stamp_date})
  • extract_years(${sales.time_stamp_date})

If prepared properly, here is the result:

correct_results.png

Now

This returns the current timestamp; no other arguments are required.

current_timestamp.png

In this instance, here's the calculation: now()

If saved properly, here is the result:

correct_timestamp_calculation.png

To_date

Changes a string of text numbers to a date, and must follow one of the following formats:

  • YYYY
  • YYYY-MM
  • YYYY-MM-DD
  • YYYY-MM-DD hh
  • YYYY-MM-DD hh:mm
  • YYYY-MM-DD hh:mm:ss
Note: The year 2018, the number 2,018, and the word 2018 are not considered the same by Analytics. In this instance, it is the word 2018 that responds to the to_date function.

In this calculation, the date was created from straight text:

to_date_function.png

The calculation: to_date("2018-05-04")

The results:

to_date_results.png

Trunc_Days (or Weeks, Months, Years)

This rounds time down to the desired closest interval. In this instance, the trunc_months function changes the sale date to the month:

trunc_months.png

The calculation: trunc_months(${sales.time_stamp_date})

If prepared properly, here are the results:

trunc_months_results.png

 

Was this article helpful?

0 out of 0 found this helpful