Hello!
I am using date_trunc in my Metabase dashboards and when I use it for a week unit, it makes the weeks start on Sunday instead of Monday. Would anyone know how to set the right behavior in Metabase?
Thanks a lot!
Hi @lrahal
Without knowing which database type you're querying or version of Metabase, then it's difficult to help.
Post "Diagnostic Info" from Admin > Troubleshooting.
Yes, here it is:
{
"browser-info": {
"language": "fr-FR",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.13+8",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.13",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.13+8",
"os.name": "Linux",
"os.version": "4.14.252-131.483.amzn1.x86_64",
"user.language": "en",
"user.timezone": "Europe/Paris"
},
"metabase-info": {
"databases": [
"snowflake",
"postgres",
"googleanalytics"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "10.18"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
}
},
"run-mode": "prod",
"version": {
"date": "2021-12-10",
"tag": "v0.41.4",
"branch": "release-x.41.4",
"hash": "471cfb9"
},
"settings": {
"report-timezone": "Europe/Paris"
}
}
}
@lrahal I don't know if you are using Postgres or Snowflake.
Latest release is 0.43.4: https://github.com/metabase/metabase/releases/latest
In fact the date_trunc starts on Sunday when I use the snowflake datawarehouse. It starts on Monday when I use the postgres database.
@lrahal Upgrade to 0.43.0 or newer, which fixes issues with start-of-week on Snowflake, which you define in Admin > Settings > Localization.
Or use this workaround: https://github.com/metabase/metabase/issues/16114#issuecomment-850837877
1 Like
Hi! We did the update of Metabase but we still have the issue with dates. See the following request:
select distinct
full_date,
{{date_start}} as date_start,
{{date_end}} as date_end
from dim.calendar
where full_date between {{date_start}} and {{date_end}}
gives date_end 5th august 10pm instead of 6th august.
Would you know why?
Thanks a lot!
@lrahal
Post "Diagnostic Info" from Admin > Troubleshooting.
And the full column type of dim.calendar.full_date
I cannot reproduce, when I create a Date filter, the it only returns a date. Something is making me think that you're using some formatting on Snowflake, but it's hard for me to tell.
Troubleshooting:
{
"browser-info": {
"language": "fr-FR",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16+8",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.16",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16+8",
"os.name": "Linux",
"os.version": "4.14.252-131.483.amzn1.x86_64",
"user.language": "en",
"user.timezone": "Europe/Paris"
},
"metabase-info": {
"databases": [
"snowflake",
"postgres",
"googleanalytics"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "10.18"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.4.1"
}
},
"run-mode": "prod",
"version": {
"date": "2022-08-16",
"tag": "v0.44.1",
"branch": "release-x.44.x",
"hash": "112f5aa"
},
"settings": {
"report-timezone": "Europe/Paris"
}
}
}
@lrahal You need to go to your Snowflake database to get the actual underlying column type.
And then you need to check your Snowflake account if you are changing formatting, or setting timezones, since there's something strange going on, which I cannot reproduce.
In Snowflake the type of the column full_date is DATE
@lrahal Are you sure you don't have custom formatting applied somewhere in your account?
I'm not going to link all of them, look in the documentation:
https://docs.snowflake.com/en/sql-reference/parameters.html#label-timestamp-output-format
Try doing: select current_date() - it should only return a date, not a timestamp or datetime.
I don't get why the problem would be on the Snowflake side while it is when applying the Metabase filter on dates that there is a problem...
