How to adjust the date/timestamp filter in Dashboard

[Tanaji]

Hi,
I am trying to display only those records as result in looker dashboard in which my date is
‘2020-05-05 00:00:00.00’ from DB but i am getting result for where date is
‘2020-05-05 01:32:18.00’.
I have filter the date field. I want result only for ‘2020-05-05 00:00:00.00’.

Thank you

[Dawid]

HI Tanaji,

We would need more information here. The timezone of the database, your Looker settings, what does the LookML look like and the timezone from which you’re accessing your instance.

[Tanaji]

TimeZone for Database is “America/Los_Angeles”

LookMl looks like :
dimension_group: dt {
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.“DATE” ;;

[Dawid]

What about Looker instance’s settings? Is it also set for America/Los_Angeles ?

[Tanaji]

yes, it is also America/Los_Angeles

[Dawid]

What does your query look like?

[Tanaji]

select * from <table_name> WHERE DATE = to_date(‘05/05/2020’,‘MM/DD/YYYY’)
This is the query i am executing in My Snowflake.
Whatever result i am getting from this query i want the same no. of rows display in my Looker dashboard

[Dawid]

What query do you see generated in the SQL tab in Looker?

[Maddie]

Hi Tanaji,

I think you should be able to achieve that if you filter on the time dimension instead of filtering on date dimension. Example:

When you apply a filter on a date, you get all timestamps in that day, i.e. 2020-05-05 01:32:18.00 is in the day 2020-05-05.

Best,
Maddie | Redkite

[Tanaji]

David i have filter Date into dashboard and applying that to a look inside the dashboard, where do i find query? please help

[Tanaji]

Hi Maddie, Thanks! this really helps but every time i will have to change the date Manually.
What if i want that date to change automatically, or i have to schedule this for every day.

[Maddie]

Hi Tanaji,

No problem! I could only think of a workaround for this, using a Yes/No field.

STEP 1: Add the timeframe ‘second’ to the date dimension:

dimension_group: created {
      type: time
      timeframes: [time, date, week, month, year, second, raw]
      sql: ${TABLE}.created_at ;;
}

STEP 2: Create a Yes/No dimension to determine if the timestamp is midnight:

dimension: is_midnight {
  type: yesno
  sql: substr(${created_second},12,8) = '00:00:00' ;;
}

STEP 3: Use this as a filter on your dashboard or tile:

There are other timeframes available, but I don’t think any of them is very useful for your use case:

Looker Documentation

dimension_group

LookML parameter: Create a set of time- or duration-based dimensions.

Hope this helps!

Best,
Maddie | Redkite

[Tanaji]

Hi Maddie,

This works, it was helpful. Thanks for help!
I appreciated your time!
Thanks again