MongoDB ODBC time portion of ISODate field not returned

[rob_nolan]

I have connected to my MongoDB instance using the MongoDB ODBC driver and can access my data but the standard ISODate objects in the database are being returned as date only i.e. the time is not included. I have tried changing the format to Date/Time in the query editor but this just appends 00:00:00 to the date regardless of what the time is in the database. Has anyone encountered this issue before? I haven't been able to find any solution and any help would be greatly appriciated. 

[rob_nolan]

--UPDATE--

I have made some progress in figuring out what's causing the behaviour I am seeing. I used the ODBC Test software to test the connection to my Mongo database and the data that is being returned. The full date including time and timezone is present so I know the date is getting to ODBC intact, so it appears that the issue is with the way PowerBI queries the data. Looking into this further I opened the advanced editor within the query editor and replaced this:

let
    Source = Odbc.DataSource("dsn=MongoDSN", [HierarchicalNavigation=true]),
    test_Database = Source{[Name="testDb",Kind="Database"]}[Data],
    test_Table = test_Database{[Name="testTable",Kind="Table"]}[Data]
in
    test_Table

with this:

let
    Source = Odbc.DataSource("dsn=MongoDSN", "select * from testTable")
in
    Source

which gives me the full date including time and timezone so the issue is almost certainly within the query. I was ab;e to find the queries within the ODBC logs:

-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:42

-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:43

1534929523:SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM ( SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA LIKE 'testDb' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) ) TABLES WHERE TABLE_NAME LIKE 'testTable';
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:43

-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:44

1534929524:SHOW KEYS FROM `testDb`.`testTable`;
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:44

1534929524:SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,A.TABLE_NAME AS FKTABLE_NAME,A.COLUMN_NAME AS FKCOLUMN_NAME,A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,A.CONSTRAINT_NAME AS FK_NAME,'PRIMARY' AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE D ON (D.TABLE_SCHEMA=A.REFERENCED_TABLE_SCHEMA AND D.TABLE_NAME=A.REFERENCED_TABLE_NAME AND D.COLUMN_NAME=A.REFERENCED_COLUMN_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = A.CONSTRAINT_NAME AND R.TABLE_NAME = A.TABLE_NAME AND R.CONSTRAINT_SCHEMA = A.TABLE_SCHEMA) WHERE D.CONSTRAINT_NAME IS NOT NULL AND A.TABLE_SCHEMA = 'testDb' AND A.TABLE_NAME = 'testTable' ORDER BY FKTABLE_CAT, FKTABLE_NAME, KEY_SEQ, PKTABLE_NAME;
1534929524:Using direct execution;
1534929524:query has been executed;
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:45

1534929525:SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,A.TABLE_NAME AS FKTABLE_NAME,A.COLUMN_NAME AS FKCOLUMN_NAME,A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,A.CONSTRAINT_NAME AS FK_NAME,'PRIMARY' AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE D ON (D.TABLE_SCHEMA=A.REFERENCED_TABLE_SCHEMA AND D.TABLE_NAME=A.REFERENCED_TABLE_NAME AND D.COLUMN_NAME=A.REFERENCED_COLUMN_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = A.CONSTRAINT_NAME AND R.TABLE_NAME = A.TABLE_NAME AND R.CONSTRAINT_SCHEMA = A.TABLE_SCHEMA) WHERE D.CONSTRAINT_NAME IS NOT NULL AND A.REFERENCED_TABLE_SCHEMA = 'testDb' AND A.REFERENCED_TABLE_NAME = 'testTable' ;
1534929525:Using direct execution;
1534929525:query has been executed;
-- Query logging
--
--  Driver name: MySQL ODBC 1.0 Driver  Version: 01.00.0000
-- Timestamp: 180822 10:18:45

1534929525:select `_id`,

    `date`

from `testDb`.`testTable`

order by `_id`;
1534929525:Using direct execution;
1534929525:query has been executed;

Can anyone spot an issue with the queries? I can't see anything obvious that would be causing the issue.

I can use the "select * from testTable" as a workaround for now but this feels like a bug to me.

[v-cherch-msft]

Hi @rob_nolan

You may try to check the value format in your data source. If it is not your case, you may submit a support ticket.

https://powerbi.microsoft.com/en-us/support/

Regards,

Cherie