Bug in date handling by LAB Faster Custom Dimensions

[Maurice_Richard]

Im trying to do a diff_days(inventory_item.issued_date (timeframe group field date raw field is timestamp), inventory_item.valid_until (date db)
Without the lab function it generates this:

CAST(CASE WHEN (UNIX_TIMESTAMP((CAST(CAST(DATE_FORMAT(inventory_item.issued_at , 'yyyy-MM-dd') AS DATE) AS TIMESTAMP))) - UNIX_TIMESTAMP((CAST(CAST(DATE_FORMAT(inventory_item.valid_until , 'yyyy-MM-dd') AS DATE) AS TIMESTAMP)))) / (60 * 60 * 24) < 0 THEN CEILING((UNIX_TIMESTAMP((CAST(CAST(DATE_FORMAT(inventory_item.issued_at , 'yyyy-MM-dd') AS DATE) AS TIMESTAMP))) - UNIX_TIMESTAMP((CAST(CAST(DATE_FORMAT(inventory_item.valid_until , 'yyyy-MM-dd') AS DATE) AS TIMESTAMP)))) / (60 * 60 * 24)) ELSE FLOOR((UNIX_TIMESTAMP((CAST(CAST(DATE_FORMAT(inventory_item.issued_at , 'yyyy-MM-dd') AS DATE) AS TIMESTAMP))) - UNIX_TIMESTAMP((CAST(CAST(DATE_FORMAT(inventory_item.valid_until , 'yyyy-MM-dd') AS DATE) AS TIMESTAMP)))) / (60 * 60 * 24)) END AS INT) `test`,

Which returns the negative values.
With the lab function on it does this:

CAST(CASE WHEN (UNIX_TIMESTAMP((DATE_FORMAT(inventory_item.issued_at , 'yyyy-MM-dd'))) - UNIX_TIMESTAMP((DATE_FORMAT(inventory_item.valid_until , 'yyyy-MM-dd')))) / (60 * 60 * 24) < 0 THEN CEILING((UNIX_TIMESTAMP((DATE_FORMAT(inventory_item.issued_at , 'yyyy-MM-dd'))) - UNIX_TIMESTAMP((DATE_FORMAT(inventory_item.valid_until , 'yyyy-MM-dd')))) / (60 * 60 * 24)) ELSE FLOOR((UNIX_TIMESTAMP((DATE_FORMAT(inventory_item.issued_at , 'yyyy-MM-dd'))) - UNIX_TIMESTAMP((DATE_FORMAT(inventory_item.valid_until , 'yyyy-MM-dd')))) / (60 * 60 * 24)) END AS INT) `test`,

Which just returns null values. And I think its the unix_timestamp that doesn't work on a date field.
Using databricks DB. 

Protip: maybe make it more clear/easier to report bugs?

[andy4]

Hi Maurice! First off, big thanks for reporting this unexpected behavior, especially with experimental features! Really appreciate the feedback. As for filing bugs, the right way is to start a support case. You can do so either through the help icon (the chat bubble with a "?" in it) at the top right of your instance UI, or by going to this webpage: help.looker.com - you will simply choose the Looker product then file your question or bug! And as always feature requests should be added in the "Product Idea?" page available in the same support icon dropdown in the looker instance.

As for your specific bug, my first thought is that since your raw field is a timestamp, it might be a good idea to use set the "datatype" property in your dimension group to "timestamp" since datetime is the default. Sometimes SQL dialects can prevent failure even without perfect SQL, so you might not have noticed that under normal use... but perhaps it is affecting this experimental feature.