Oracle date fields hiding time part

Bug reports
1

Since the 0.41 version, to fix the #7494 issue, date field output format was altered to hide the time part.

However, Oracle date data type fields store date and time information up to second precision and without time zone, as especified here.

Oracle timestamp data type is used to store date and time with subsecond precision and time zone.

The following query was ran by Metabase 0.40.7 against an Oracle 19c Database. According to the documentation,
SYSDATE function returns current date and time and the data type of the returned value is Oracle DATE. As expected, the output is correctly formatted as datetime.

image
image1432×803 32 KB

This second image shows the same query ran by Metabase 0.42 against an Oracle 19c Database. This time, Metabase shows only the date part.

image
image1290×572 16.7 KB

The workaround to show the time part from an Oracle date filed is to convert the Oracle date field to Oracle timestamp. But it can be a lot of rework and it can slow some queries.

It seems that Oracle date fields should be treated as datetime type.

2

Hi @noritomi
The SYSDATE returns a DATE type to Metabase, which is why it is handled as such.
You would probably need to build your own driver if you require a different expected type:
https://github.com/metabase/metabase/blob/master/modules/drivers/oracle/src/metabase/driver/oracle.clj#L45

3

Hi @flamber

Thank you for your suggestion! I will try that.

But, just to clarify, I emphasize that this is not a specific need. Oracle does not have a date-only, no-time data type like Postgres and other RMDBS. Dates in Oracle are actually datetime data type.

4

@noritomi Okay, Oracle is filled with strange quirks. I'm glad I'm not managing that database anymore.
You can file a bug, but it seems like the problem is Oracle, since if you return a DATETIME or TIMESTAMP, which Oracle also supports, then it is shown as expected.