CODEWITHSUNDEEP
mongodb
Mathias B.
Mathias B.

Reputation: 353

MongoDB - datetime type for mongosqld

I have a collection in MongoDB that have a Date field :

date: 2021-02-17T18:40:01.000+00:00

I want to expose this collection to a BI tool thanks to MongoDB BI Connector, mongosqld. I used mongodrdl to create the data model.

mongodrdl converts MongoDB's Date type to MySQL's timestamp type. When i read the date column in a BI application, the time is only zeros :

17/02/2021 00:00:00

This is catastrophic because i need the time. I tried to edit the drdl generated by mongodrdl and put the SqlType to datetime. But when i restart mongosqld i get the following error :

unable to create column "date" from drdl: unsupported SQL type: "datetime" on column "date"

How can i preserve the time for this date field to be properly exposed to BI tools ?

Upvotes: 0

Views: 135

Answers (1)

D. SM
D. SM

Reputation: 14490

date: 2021-02-17T18:40:01.000+00:00

This is a stringified (iso8601) representation of a timestamp. If this is how your timestamps are stored, they are of the wrong type (string, not timestamp) and hence produce zeroed SQL timestamps.

To fix, store the timestamps as timestamps (BSON date type).

Upvotes: 1

Related Questions