Cannot group by hour after upgrade from 0.34 to 0.42.4

#1

Hello guys,
first, let me thank you for putting so much effort into Metabase!

Now, in production I'm using v0.34, we started another instance with Metabase upgraded to v0.42.4 as a test with production config. All questions and dashboards are working except questions where I'm using GROUP BY HOUR. If I switch to DAY on interactive filter, it works.

So I converted question to SQL:

SELECT
trunc("SY_TEVRON"."TIMERRESULTS"."SCRIPTSTARTTIME", 'dd') AS "SCRIPTSTARTTIME",
avg("SY_TEVRON"."TIMERRESULTS"."TEST_DUR") AS "avg"
FROM "SY_TEVRON"."TIMERRESULTS"
WHERE ("SY_TEVRON"."TIMERRESULTS"."SCRIPTNAME" = 'TMWEB-B2B-BlokaceInternet'
AND "SY_TEVRON"."TIMERRESULTS"."TIMERNAME" = 'b2b_zvolitSpravuCisla'
AND "SY_TEVRON"."TIMERRESULTS"."TIMERSTATUS" = 'Pass')
GROUP BY trunc("SY_TEVRON"."TIMERRESULTS"."SCRIPTSTARTTIME", 'dd')
ORDER BY trunc("SY_TEVRON"."TIMERRESULTS"."SCRIPTSTARTTIME", 'dd') ASC

When I change in SQL dd to hh it works perfectly but I would love to make it working as a standard question.

Am I missing something or doing something wrong?

Thank you for help.

T.

#2

Hi @ticka
Post "Diagnostic Info" from Admin > Troubleshooting.
And can you explain what the problem is? Are you seeing errors, if so post them. Perhaps screenshots would help understanding the problem better.

#3

Hi @flamber,

I am sorry, forgot to post it. Error is:
Unsupported temporal bucketing: You can't bucket a :type/Date Field by :hour.

Screenshot here:

image
image.png1315×727 45.5 KB

After changing granularity to Day it is working:

image
image.png1634×716 46.8 KB

Diagnostic info here:
{
"browser-info": {
"language": "en-US",
"platform": "Linux x86_64",
"userAgent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:99.0) Gecko/20100101 Firefox/99.0",
"vendor": ""
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_212-b04",
"java.vendor": "IcedTea",
"java.vendor.url": "https://icedtea.classpath.org",
"java.version": "1.8.0_212",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.212-b04",
"os.name": "Linux",
"os.version": "3.10.0-1062.9.1.el7.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"oracle",
"postgres",
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.7"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.42.4",
"date": "2022-04-07",
"branch": "release-x.42.x",
"hash": "7c3ce2d"
},
"settings": {
"report-timezone": "Europe/Berlin"
}
}
}

Thx for help,
T.

#4

@ticka
So this problem is only happening on Oracle?
What is the actual database column type of the "Date" column? If it is DATE, then that's the problem. It should be DATETIME or TIMESTAMP.

Which version of ojdbc8.jar are you using?

You should upgrade to Java 11: https://www.metabase.com/docs/latest/operations-guide/java-versions.html

#5

Actually I cannot tell if it is oracle only or not. Hourly data is only in Oracle database. We have other data in MYSQL but these are only daily.

I will check with server admin about the version and ask him to upgrade if it is not v11. Good thing is, that old version is still running correctly with this database :+1:

#6

Hi @flamber,

we now have Java11 in place and still got the same error:
Unsupported temporal bucketing: You can't bucket a :type/Date Field by :hour.

In Data model for this oracle DB, SCRIPTSTARTTIME is type of "Creation date". But the same value is set in old version which is working. I tried to set it to "Createion timestamp", discarder cache and rescan table but still, no success.

Or where else can i change the column type?

T

#7

@ticka You cannot change column type in Metabase. The field type is just semantic type (formatting etc).

I don't know which version of ojdbc8.jar you are using. Make sure it's 19.3 or newer.
https://www.metabase.com/docs/latest/administration-guide/databases/oracle.html

And change your database column type to a datetime or timestamp. You cannot do that in Metabase, you have to do that on your database.

#8

Hi @flamber,
finally I managed in our corporation to change the column type. After rescaning it is Working.

Thank you for your advices.

Cheers,
T.