Filter Not Updating with correct datatype

Get Help
#1

I have a database table that had a text (varchar) field. This was incorrect and updated to a datetime with the table/column having the same name.

Sync Database Schema and a Re-scan of field values were ran from Admin -> Databases

image

Metabase picks up the correct data type (DateTime)

image

However when this table is selected and a filter applied, it is still seeing it as a text (varchar) and does not allow the associated datetime filter options

image

Are we able to reset the filter options to recheck the database data type?

#2

Hi @pacsman
Post "Diagnostic Info" from Admin > Troubleshooting.
And the metadata for one of the fields by going to the URL /api/field/123, where 123 is the field ID, which you can see by going to Admin > Data Model > (db) > (table) > (column) :gear: > look in the URL that looks like .../123/general

#3

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.102 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "Cp1252",
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "1.8.0_333-b02",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_333",
"java.vm.name": "Java HotSpot(TM) Client VM",
"java.vm.version": "25.333-b02",
"os.name": "Windows Server 2016",
"os.version": "10.0",
"user.language": "en",
"user.timezone": "Australia/Sydney"
},
"metabase-info": {
"databases": [
"sqlserver"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "14.3"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.3.2"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.43.2",
"date": "2022-05-31",
"branch": "release-x.43.x",
"hash": "433d533"
},
"settings": {
"report-timezone": "Australia/Sydney"
}
}
}

#4

{"description":null,"database_type":"datetime","semantic_type":null,"table_id":54,"coercion_strategy":null,"table":{"description":null,"entity_type":"entity/GenericTable","schema":"dbo","db":{"description":null,"features":["full-join","basic-aggregations","standard-deviation-aggregations","expression-aggregations","foreign-keys","right-join","left-join","native-parameters","nested-queries","expressions","binning","inner-join","advanced-math-expressions"],"cache_field_values_schedule":"0 0 13 * * ? *","timezone":"UTC","auto_run_queries":false,"metadata_sync_schedule":"0 43 * * * ? *","name":"Sectra Kestral","settings":null,"caveats":null,"creator_id":1,"is_full_sync":true,"updated_at":"2022-06-14T01:09:02.083409Z","cache_ttl":null,"details":{"additional-options":null,"ssl":false,"instance":"SQLEXPRESS","db":"RISPACS","password":"MetabasePass","let-user-control-scheduling":false,"rowcount-override":null,"port":63797,"advanced-options":true,"host":"GFDCAPP-MAS035","tunnel-enabled":false,"user":"webuser"},"is_sample":false,"id":34,"is_on_demand":false,"options":null,"engine":"sqlserver","initial_sync_status":"complete","refingerprint":null,"created_at":"2022-06-06T04:55:42.263Z","points_of_interest":null},"show_in_getting_started":false,"name":"RISPACS","caveats":null,"updated_at":"2022-06-06T05:00:29.613Z","active":true,"id":54,"db_id":34,"visibility_type":null,"field_order":"database","initial_sync_status":"complete","display_name":"Rispacs","created_at":"2022-06-06T04:55:44.856Z","points_of_interest":null},"name":"Verification","fingerprint_version":5,"has_field_values":"none","settings":null,"caveats":null,"fk_target_field_id":null,"dimensions":[],"updated_at":"2022-06-08T05:43:01.962Z","custom_position":0,"effective_type":"type/Text","active":true,"nfc_path":null,"parent_id":null,"id":412,"last_analyzed":"2022-06-06T05:00:29.691Z","position":31,"visibility_type":"normal","preview_display":true,"display_name":"Verification","database_position":31,"name_field":null,"fingerprint":{"global":{"distinct-count":7947,"nil%":0.0045},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":13.937}}},"created_at":"2022-06-06T04:55:46.356Z","base_type":"type/DateTime","points_of_interest":null}

#5

image
image.png999×574 27 KB

#6

@pacsman I don't know when you changed the type, but last sync change was on June 8th.
If there are errors during sync, then that could be the cause of the change not getting through.
Check your logs during manual sync - Admin > Troubleshooting > Logs.

You could also try to set the Field Type to "Creation timestamp".

Otherwise force Metabase to do fingerprinting again - see the workaround and run sync again:
https://github.com/metabase/metabase/issues/18074

#7

Thanks @flamber, for some reason the logs are not loading. I may need to go back and look at the write permissions on the jar directory.
I was able to update via the creation date method.
Will look at the fingerprinting and will try to push a refresh

#8

@pacsman Upgrade to Java 11: https://www.metabase.com/docs/latest/operations-guide/java-versions.html