Comparing Dates with Liquid

[Ricardo_MR]

Usually, if we have a case in which we need to compare dates within Looker , we do that using SQL (like filtering results so the dates are between a range of dates) and operators like <, >, etc., but what if we want to use a date parameter to build a dynamic query (for a derived table for example), to optimize what SQL query we send to the database (table name, fields, clauses, etc.) like in this article: Dynamically Query Tables Using Parameters.

In that case, we would use Liquid to compare date values and add the statements we want to include in our dynamic query according to our logic, but, we cannot compare dates directly as with SQL, as an example, lets say that we have a parameter defined like this (we will use the default value in this post): 

parameter: test_date {

  type: date
  
  default_value: "2022-01–01"
}

And we want to change the table name depending on a (hardcoded) date range:

view: test_view {

  derived_table: {
    sql:
      SELECT
        
        test.id AS test_id,
        test.name AS test_name
      
      {% assign start_date = '2020-01-01' %}
      
      {% assign end_date = '2023-01-01' %}
      
      {% if test_date._parameter_value >= start_date and test_date._parameter_value <= end_date %}
      FROM `dataset.table01` AS test
      
      {% else %}
      
      FROM `dataset.table02` AS test
      
      {% endif %}
      GROUP BY test_id;;
  }
  .
  .
  .
}

This will generate this SQL query:

SELECT
    
  test.id AS test_id,
  test.name AS test_name

FROM `dataset.table02` AS test

GROUP BY test_id

That is because test_date._parameter_value will be used as a date (in format) but start_date and end_date are only strings, so the comparison will not evaluate as expected, to fix this we will need to format these values to something we can use, in this case, Epoch-Seconds using the date filter available on Liquid.

Using this article: Easy Date Formatting with Liquid, we know that we can use '%s' to format a date to Epoch-Seconds, and then we can safely compare the numeric values like this (we need another variable for our parameter, the use of | causes syntax parser errors inside an if conditional):

view: test_view {

  derived_table: {
    sql:
      SELECT
        
        test.id AS test_id,
        test.name AS test_name
      
      {% assign start_date = '2020-01-01' | date: '%s' %}
      
      {% assign end_date = '2023-01-01' | date: '%s' %}
      
      {% assign my_date = test_date._parameter_value | date: '%s' %}
      
      {% if my_date >= start_date and my_date <= end_date %}
      FROM `dataset.table01` AS test
      
      {% else %}
      
      FROM `dataset.table02` AS test
      
      {% endif %}
      GROUP BY test_id;;
  }

  .
  .
  .
}

This will give us the expected SQL query in this case (default value for parameter test_date😞

SELECT
    
  test.id AS test_id,
  test.name AS test_name

FROM `dataset.table01` AS test

GROUP BY test_id

[paulywog]

@Ricardo_MR is it possible to achieve this strategy with dynamic dates?

Specifically I'm trying to utilize the start date from a date filter {% date_start date_filter %} but struggling to assign the date_start to a liquid variable. I'm using Snowflake, which appears to materialize {% date_start date_filter %} as something like date_add('days', -6, current_date()), if the date_filter is set to "last 7 days".

So in your example, my desired code would become:

view: test_view {

  derived_table: {
    sql:
      SELECT
        
        test.id AS test_id,
        test.name AS test_name
      
        {% assign start_date = {% date_start date_filter %} | date: "%d" %}
      
        {% assign nowDate = "now" | date: "%d" %}
        {% assign diffDays = nowDate | minus: dateStart %}
        {% if diffDays < 14 %}
      FROM `dataset.table01` AS test
      
      {% else %}
      
      FROM `dataset.table02` AS test
      
      {% endif %}
      ;;


filter: date_filter {
type: date
}

Thank you for your help!