Work with Dates in Looker Without Unnecessary SQL Date Conversions

[rif]

How can I configure Looker to handle date fields without unnecessary SQL conversions, while still enabling effective date filtering in the Looker UI?

It looks like date_raw is not an option for the dimension because it cannot be selected. It seems that due to a bug in Looker, I am able to change an existing date to date_raw, although it does not appear in the selection area. See reply below 

To eliminate these conversions, I tried using date_raw in my Looker model. But then cant filter "Cannot filter on fact_customers_daily.snapshot_simple. Please remove the filter on this field."

Here's the situation:

1. I have a column named SnapshotDate in my database, which is of type DATE and stored in the format YYYY-MM-DD. The database is Synapse Dedicated SQL pool (formerly SQL DW).

2. When Looker generates SQL queries, it automatically applies unnecessary CONVERT functions to the SnapshotDate. Here's an example of the generated SQL query:

SELECT TOP (1000)
    (CONVERT(VARCHAR(10), [SnapshotDate], 120)) AS [fact_customers_daily.snapshot_simple4],
    COUNT(CASE WHEN (fact_customers_daily.x= 'Active') THEN 1 ELSE NULL END) AS [fact_customers_daily.count_active_customers]
FROM
    [dimensional].[fact_customers_daily] AS [fact_customers_daily]
WHERE
    ([SnapshotDate] >= DATEADD(day, -6, CONVERT(DATETIME, CONVERT(VARCHAR, SYSDATETIMEOFFSET() AT TIME ZONE 'Greenwich Standard Time', 102), 120)))
    AND
    ([SnapshotDate] < DATEADD(day, 7, DATEADD(day, -6, CONVERT(DATETIME, CONVERT(VARCHAR, SYSDATETIMEOFFSET() AT TIME ZONE 'Greenwich Standard Time', 102), 120))))
GROUP BY
    (CONVERT(VARCHAR(10), [SnapshotDate], 120))
ORDER BY
    1 DESC

My question is:

How can I configure Looker to work with date fields without it automatically converting dates in the SQL queries, while still retaining the ability to use date filters in the Looker UI?

I want to:

• Avoid unnecessary CONVERT functions in the generated SQL.
• Retain full date filtering capabilities in Looker.

Notes for clarity:

1. Conversion in WHERE Clause: I understand the rationale behind converting dates in the WHERE clause to ensure proper comparison, but in this case, SnapshotDate retains its native format,
2. Formatting Dates: Any required presentation formatting can be handled via  Liquid formatting.

Any guidance or suggestions on how to achieve this would be greatly appreciated!

Thank you!

[rif]

It looks like date_raw is not an option for the dimension because it cannot be selected. It seems that due to a bug in Looker, I am able to change an existing date to date_raw, although it does not appear in the selection area.

[asliceoftom]

Hi, 

I can repeat the same behaviour, but I also see it is documented here: Dimension, filter, and parameter types  |  Looker  |  Google Cloud

Why is using date_raw not allowed on Looker? We think it will improve the performance of our queries

[andy4]

First off, if you really want to test the performance difference, you can use the "date_raw" version if you use the "Share -> Expanded URL" link available in the gear icon next to "Run" - just change the reference of _date to _raw and then visit the edited URL. This isn't useful for everyday use, but it will allow you to test your hypothesis.

Next, if your date field is stored in a type other than datetime (looker's default expectation), it is best to use the "datatype" property in your date dimensions and dimension_groups. That should improve the generated SQL.

In my experience, a DATE(myfield) function will simply be ignored by the SQL compiler when the myfield is already cast as a date type.

Speaking for the product, I do see your frustration that as a SQL/LookML developer it would be nice to have access to the _raw version. However, the vast majority of users are not developers and therefore in order to prevent confusion between the _raw or _date (or time) version, the raw is hidden by default. This will allow things like application or user-specific timezone conversion, better data formatting for the viz, etc.

And as all SQL developers know... timezones are hard! Check out this article (and the first reply, written by yours truly) for more detailed info on the myriad ways Looker helps you get them right: How Looker does timezones and how to troubleshoot them

[rif]

@andy4 , thank you for your response, and I apologize for the delay in my reply

1. In the Synapse Directed Pool (SQL DW), we're handling the data type as either DateTime or Date. However, for this particular section, you can consider it similar to Azure SQL Server, utilizing a columnar table type.

2. As observed in SQL Server, when encountering a Non-Sargable field, it projects to create the data. For instance, this would occur when using Year(EnqueuedDate).

3. Regarding Looker's behavior, I'm unsure why there's a double conversion when the field is already typed as date. Could this issue be related to the configuration settings of SQL Server or Synapse Directed Pool with Looker? We are not utilizing timezones in our setup.

4. Here's the LookerML configuration for your reference:

## Dates
dimension_group: snapshot_at {
  group_label: "Dates and Date Filters"
  type: time
  timeframes: [
    day_of_month,
    day_of_week,
    raw,
    date,
    week,
    month,
    quarter,
    year
  ]
  convert_tz: no
  datatype: date
  sql: ${TABLE}.SnapshotDate ;;
}

1. The schema is defined as follows:

CREATE TABLE x.x
(
  [Id] [varchar](50) NULL,
  [SnapshotDate] [date] NOT NULL
)
WITH
(
  DISTRIBUTION = HASH ( [Id] ),
  CLUSTERED COLUMNSTORE INDEX ORDER ([SnapshotDate])
)
GO

Could you please suggest any potential adjustments to reduce the unnecessary conversions? Any insights or adjustments you could recommend would be greatly appreciated.

[rif]

@andy4 I hope you’re doing well! I just wanted to check in on my last post. Thanks! https://www.googlecloudcommunity.com/gc/Modeling/Work-with-Dates-in-Looker-Without-Unnecessary-SQL-D... 

[andy4]

First off, thanks for the great documentation of this one. I have seen other dialects able to NOT do the conversion when the datatype is already correct... like, when the lookml datatype matches the actual sql datatype and the requested timeframe is also the same (in all three cases they are date here) - so my hunch is this is an opportunity for improvement in our SQL writing for SQL Server. 

I recommend opening a support case with a link to here and, ideally, copy pasting all this info there too. That's your best bet to get this in front of an engineer.

From my perspective, your issue is that you need date filter options - you could simply create a dimension of type: number or maybe string and reference the ${date_raw} in the sql... then maybe add a value_format which makes it display as a date. But the only way to get the date filter options is to declare the dimension as type date... and that brings you to your situation.

As far as what to do right now, I guess I'd say try what I just suggested, then use a filter: field set as type date, and use that to point your filter interaction at the date field in a yesno. Something like
filter: date_hack {type: date}
dimension: is_date_hack {
  type: yesno
  sql: {% condition date_hack %} ${snapshot_at_raw} {% endcondition %}
}
dimension: date_hack_for_selecting {
  type: number
  sql: ${snapshot_at_raw} ;;
  value_format: "m-d-yyyy"  
}

Then just filter on those first two fields, while selecting that last one. Lemme know if you file the support case and if my hack works! I didnt test any of it so you might need to fiddle a bit.