Wrong Day of week

[AQuer]

Hi,
I need to extract day of week from date.
If I use native function, it gets bad result

17/4/24 was wednesday, function returns thursday ("dimarts")

Being used native function:

and calculated field as

what's on? can we change anything?
thanks,
anna

{
"browser-info": {
"language": "ca",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "11.0.13+10-LTS-370",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "https://openjdk.java.net/",
"java.version": "11.0.13",
"java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
"java.vm.version": "11.0.13+10-LTS-370",
"os.name": "Linux",
"os.version": "3.10.0-1160.25.1.el7.x86_64",
"user.language": "en",
"user.timezone": "Europe/Madrid"
},
"metabase-info": {
"databases": [
"postgres",
"oracle"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "13.3"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.7.2"
}
},
"run-mode": "prod",
"version": {
"date": "2024-04-10",
"tag": "v0.49.4",
"hash": "0fdefcf"
},
"settings": {
"report-timezone": null
}
}
}

[TonyC]

If you go to Admin -> Localization Settings .. Can you check the first day of week setting there

e

[AQuer]

Hi Tony, thanks a lot for your answer.
I checked that before and didn't work.

that's working days (it should be monday-friday with data, not sunday "diumenge")
this is the configuration I have now

starts on "dilluns" monday AND THE PLOT STARTS ON "Dimarts" THURSDAY!!!

I change that to "diumenge" (sunday)
and the result is that sunday apears on first position, but data is still wrong

it's something more... if you use day of week with your data, you get right results?

[AndrewMBaines]

Is it wrong when displayed as a table? I had a problem with MS SQL when the first day of the week in SQL was a Sunday rather than a Monday. The table was correct, the chart wrong.

[AQuer]

you mean the same question viewed as table? yes, is still wrong

diumenge-->sunday it must be around zero

[smartpanther]

I have the same Issue, connected to MS SQL.

Date Format is right and start dday also set to monday. In the Raw DB the date is correct and in the visualization the day is 1 day before. We need a solution here, without right date the reports are a mesh. Anybody with an idea?

[Luiggi]

Check the reporting timeline. And if there’s still an error there post the screenshots here and version

[smartpanther]

Reporting timeline is correct, same like the database to Europe/Berlin. SQL Database has UTC+1. Table content is right, the dates are Mo-Fri. But the Graphic show us Saturdays or Sundays. Version of Metabase is: 0.52.8 Thank you for your support

[AQuer]

Hi. Long time after, we still have same problem.
Days appear with wrong label. That means all our analyses are wrong!

This is because of the formula is behind has this sql code

When you write a native question, you put this formula

SELECT trim(TO_CHAR(DATA_COMPUT, 'DAY'))as DIA_SETMANA

FROM PROMEC_COMPTAPERSONES
where 1=1 [[and {{dataSel}}]]

and then the day of week it's correct,

Can we fix this? tha'ts a very important mistake.

[marcos]

Hi there,

This bug seems to happen because your Oracle DB has a start of week different from the default (this depends on the NLS_TERRITORY parameter), but Metabase assumes the Oracle DB start of week is Sunday, so the offsets it adds are wrong.
I created a bug report here, please upvote and subscribe to it to get notified when it is fixed.

Although not ideal, as possible workarounds, you could set the "First day of the week" setting in Metabase to Sunday, then the weekday numbers will be correct, but the weekday names will still be off. Another option is to avoid calculating week days in the query builder and trusting the weekdays from the database by using SQL TO_CHAR(..., 'd') and TO_CHAR(..., 'Day'), and building Query Builder questions on top of that.

[AQuer]

thanks!!
I will use query functions while waiting for Metabase to fix it!

[tansuozcelebi]

i’m not quite sure it will so, but please check if there is an option of iso_week and iso day number

maybe you can search and understand if there’s anything