Query dates, csv and time zone

[Tamar_Nirenberg]

Hi,

I perform a query which creates the collection “cons_2022_second_part”, so later I can use mongoexport to keep it as a csv file:

{ $match : {"meterConsumptionDeltas" : 0,    
                 "meterId" : "22056.3"  
                 ,"receivedTime" : { $gte: ISODate("2022-12-28T00:00:00.004Z"),
                                              $lte: ISODate("2022-12-31T00:23:59.004Z")}
            }},  
{$project: {"repeaterNumber":1,"meterConsumptionDeltas" : 1,"diameter":1,"externalName":1,"meterId":1,"receivedTime": 1} }
,{$out:"cons_2022_second_part"}
]);

The output table has 2 documents with these dates:

{
“meterId” : “22056.3”,
“externalName” : “01-200021386-14”,
“diameter” : 2,
“receivedTime” : ISODate(“2022-12-29T00:00:00.000+02:00”),
“meterConsumptionDeltas” : 0,
“repeaterNumber” : NumberLong(162)
},

{
“meterId” : “22056.3”,
“externalName” : “01-200021386-14”,
“diameter” : 2,
“receivedTime” : ISODate(“2022-12-31T00:00:00.000+02:00”),
“meterConsumptionDeltas” : 0,
“repeaterNumber” : NumberLong(162)
}

However. when I use mongoexport, this is the dates shown:

mongoexport --db mydb --collection cons_2022_second_part --fields “meterId”,“externalName”,“diameter”,“receivedTime”,“meterConsumptionDeltas”,“repeaterNumber”
–username “myuser” --password “mypassword” --type csv --out cons_2022_second_part2.csv

meterId,externalName,diameter,customerName,receivedTime,meterConsumptionDeltas,repeaterNumber
22056.3,01-200021386-14,2,2022-12-30T22:00:00.000Z,0,162
22056.3,01-200021386-14,2,2022-12-28T22:00:00.000Z,0,162

why is the difference between the dates in output table and the csv?

Thanks

[Tarun_Gaur]

Hello @Tamar_Nirenberg ,

Can you answer few questions for better understanding of your use-case?

• MongoDB Version
• Are you only concerned about difference in dates due to timezone(this is due to time zone as discussed below)? Are there any other concerns?
• MongoDB official tools usually show the timezone in UTC. Are you using other tools to produce this result? What tool are you using?

Additionally, if you just concerned about the difference in dates between the output table and mongoexportz it is due to the difference in time zones. In the output table, the dates are shown with the timezone offset +02:00. However, when you export the data to a CSV file using mongoexport, the dates are shown in UTC time zone with the timezone offset Z, which means zero offset. So, the exported dates are actually the same as the original dates in the database, but shown in UTC time zone instead of the original time zone.

As per documentation - Time Representations in MongoDB

MongoDB stores times in UTC by default, and converts any local time representations into this form. Applications that must operate or report on some unmodified local time value may store the time zone alongside the UTC timestamp, and compute the original local time in their application logic.

Example

In the MongoDB shell, you can store both the current date and the current client’s offset from UTC.

var now = new Date();db.data.save( { date: now, offset: now.getTimezoneOffset() } );

You can reconstruct the original local time by applying the saved offset:

var record = db.data.findOne();var localNow = new Date( record.date.getTime() -  ( record.offset * 60000 ) );

Regards,
Tarun

[Tamar_Nirenberg]

Thank you @Tarun_Gaur