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
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.
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” ;;
What about Looker instance’s settings? Is it also set for America/Los_Angeles
?
yes, it is also America/Los_Angeles
What does your query look like?
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
What query do you see generated in the SQL tab in Looker?
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
David i have filter Date into dashboard and applying that to a look inside the dashboard, where do i find query? please help
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.
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:
Hope this helps!
Best,
Maddie | Redkite
Hi Maddie,
This works, it was helpful. Thanks for help!
I appreciated your time!
Thanks again