Hi,
when running a query on mongo, we continuously face this error. What is strange is that dremio doesn’t recognize the timestamp as a timestamp but as a date. It however is able to extract the exact hours, minutes etc.
The query runs without any issue in Dremio but when we run from Tableau we get the above mentioned error if we want to group the data by any of the timestamp fields. I’ve also uploaded the query profile generated by dremio when running from Tableau.
1896ecec-4476-42bc-b9e7-4bddb7cd9082.zip (119,8 KB)
Here’s the query in dremio:
SELECT “_id”, type, loc, cast(ts as timestamp) as ts, cast(extract(year from ts) as varchar(4)) as tsyear, cast(extract(month from ts) as varchar(2)) as tsmonth, cast(extract(day from ts) as varchar(2)) as tsday,
cast(extract(hour from ts) as varchar(2)) as tshour, cast(extract(minute from ts) as varchar(2)) as tsminutes, “particleMeasurements”.“meas”[‘sampleTime’] AS sampleTime, “particleMeasurements”.“meas”[‘status’][‘statusValue’] AS statusValue, “particleMeasurements”.“meas”[‘status’][‘validationMode’] AS validationMode, “particleMeasurements”.“meas”[‘status’][‘calibrationExpired’] AS calibrationExpired, “particleMeasurements”.“meas”[‘status’][‘laser’][‘laserOK’] AS statusLaserOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘powerOK’] AS statusLaserPowerOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘currentOK’] AS statusLaserCurrentOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘supplyOK’] AS statusLaserSupplyOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘lifeOK’] AS statusLaserLifeOK, “particleMeasurements”.“meas”[‘status’][‘flow’][‘flowOK’] AS statusFlowOK, “particleMeasurements”.“meas”[‘status’][‘flow’][‘noFlow’] AS statusNoFlow, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘particleOverflow’] AS statusInstrumentParticleOverflow, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘instrumentOK’] AS statusInstrumentOK, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘tresholdHigh’] AS statusInstrumentTresholdHigh, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘tresholdLow’] AS statusInstrumentTresholdLow, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘samplerOK’] AS statusInstrumentSamplerOK, “particleMeasurements”.“meas”[‘status’][‘photoAmp’][‘supplyOK’] AS statusPhotoAmpSupplyOK, “particleMeasurements”.“meas”[‘status’][‘photoAmp’][‘backgroundOK’] AS statusPhotoAmpBackgroundOK, “particleMeasurements”.“meas”[‘status’][‘photoAmp’][‘photoAmpOK’] AS statusPhotoAmpOK, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch1’] AS particleChannelCh1, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch2’] AS particleChannelCh2, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch3’] AS particleChannelCh3, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch4’] AS particleChannelCh4, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch1’] AS analogChannelCh1, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch2’] AS analogChannelCh2, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch3’] AS analogChannelCh3, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch4’] AS analogChannelCh4, “particleMeasurements”.“meas”[‘values’][‘technical’][‘laserSupply’] AS laserSupply, “particleMeasurements”.“meas”[‘values’][‘technical’][‘laserVoltage’] AS laserVoltage, “particleMeasurements”.“meas”[‘values’][‘technical’][‘backgroundVolt’] AS backgroundVolt, “particleMeasurements”.“meas”[‘values’][‘internalTemps’][‘head’] AS internalHeadTemp, “particleMeasurements”.“meas”[‘values’][‘internalTemps’][‘base’] AS internalBaseTemp
FROM mongoPfizerAWS.testPfizer.particleMeasurements AS particleMeasurements