.NET DateTimeOffset Ticks - Convert to a Proper Date Time

Working with Data
,
1

If the application written in .NET is storing the audit columns (created date and modified date) in the below representation:

"createdDate" : [
                NumberLong("637672005879114577"),
                0
        ],
"modifiedDate" : [
                NumberLong("637672005879114577"),
                0
        ]

Team is using the Serializer as referred in below:
https://github.com/mongodb/mongo-csharp-driver/blob/master/src/MongoDB.Bson/Serialization/Serializers/DateTimeOffsetSerializer.cs

[DateTimeOffset.Ticks Property (System) | Microsoft Docs](DateTimeOffset.Ticks Property (System) | Microsoft Learn)

Is it possible to convert the above date time representation in Ticks to a proper Date Time in Mongo DB? The above representation in the collection may be understandable for .NET code but certainly not beneficial to anyone who is working data on the backend or for any aggregations or other logic if i am not able to get to its Date Time.

Is it a normal practice to store the information in such format as shown above?

Thanks,
Vikram

2

Hi @Vikram_Bade

     public class Sample
        {
            public DateTime CreatedDate { get; set; }
            public DateTime ModifiedDate { get; set; }
        }

Do not decorate anything for the DateTime property.

The result will be

{
    "_id" : ObjectId("6141642cb699051c8aa27b6f"),
    "CreatedDate" : ISODate("2021-09-15T03:10:34.000Z"),
    "ModifiedDate" : ISODate("2021-09-15T03:10:34.002Z")
}

Let me know this your expected result.

3

Sudhesh,

Do you mean to say that you should store the date from .NET application in proper Date/ISO Date format into Mongo DB as mentioned by you?

But, that is not the challenge I am having. The application is already storing the value the way I have mentioned in my message. So, the Question is, is it possible to convert that to an appropriate Date Time.

For example, it seems like the value [NumberLong(“637672005879114577”),0] is equivalent to UTC TIME : 14-09-2021 07:16:27

4

Ok, I was able to figure this out. The below Aggregation Pipeline would convert the ticks stored into a proper date time. Posting here in case it can help someone:

db.Customer.aggregate([
   {
     $project:
      {
		 _id:0,
		 createdDate:1,
         ticksforcreatedDate : { $arrayElemAt: [ "$createdDate", 0 ] },
		 modifiedDate:1,
		 ticksformodifiedDate : { $arrayElemAt: [ "$modifiedDate", 0 ] },
      }
   },
   {
		$addFields :
			{
				tickMilliSecondsforcreatedDate : { $divide : ["$ticksforcreatedDate",10000] }
				,tickMilliSecondsformodifiedDate : { $divide : ["$ticksformodifiedDate",10000] }
				,startingdate : new Date(ISODate('0001-01-01 00:00:00'))
			}
   },
   {
		$addFields :
			{
				createdDateTime : { $add : ["$startingdate","$tickMilliSecondsforcreatedDate"] }
				,modifiedDateTime : { $add : ["$startingdate","$tickMilliSecondsformodifiedDate"] }
			}
   },
   {
		$project : 
			{
				createdDate : 1
				,createdDateTime : 1
				,modifiedDate : 1
				,modifiedDateTime : 1
			}
   }
]).pretty()

The below is the output where it shows you the Dates in Ticks and its appropriate Date Time Values:

{
        "createdDate" : [
                NumberLong("637667896484374765"),
                0
        ],
        "modifiedDate" : [
                NumberLong("637667896484374765"),
                0
        ],
        "createdDateTime" : ISODate("2021-09-09T13:07:28.437Z"),
        "modifiedDateTime" : ISODate("2021-09-09T13:07:28.437Z")
}
{
        "createdDate" : [
                NumberLong("637671281587939656"),
                0
        ],
        "modifiedDate" : [
                NumberLong("637671281587939656"),
                0
        ],
        "createdDateTime" : ISODate("2021-09-13T11:09:18.794Z"),
        "modifiedDateTime" : ISODate("2021-09-13T11:09:18.794Z")
}
{
        "createdDate" : [
                NumberLong("637671334694957683"),
                0
        ],
        "modifiedDate" : [
                NumberLong("637671334694957683"),
                0
        ],
        "createdDateTime" : ISODate("2021-09-13T12:37:49.496Z"),
        "modifiedDateTime" : ISODate("2021-09-13T12:37:49.496Z")
}
{
        "createdDate" : [
                NumberLong("637671337131893121"),
                0
        ],
        "modifiedDate" : [
                NumberLong("637671337131893121"),
                0
        ],
        "createdDateTime" : ISODate("2021-09-13T12:41:53.189Z"),
        "modifiedDateTime" : ISODate("2021-09-13T12:41:53.189Z")
}
{
        "createdDate" : [
                NumberLong("637672005879114577"),
                0
        ],
        "modifiedDate" : [
                NumberLong("637672005879114577"),
                0
        ],
        "createdDateTime" : ISODate("2021-09-14T07:16:27.911Z"),
        "modifiedDateTime" : ISODate("2021-09-14T07:16:27.911Z")
}
1 Like
5

You can simply do this

db.Customer.aggregate([
   {
     $project:
      {
		 _id: 0,
		 createdDate: 1,
                 createdDateTime : { $add: [new Date(ISODate('0001-01-01 00:00:00')), {$divide: [{ $arrayElemAt: [ "$createdDate", 0 ] }, 10000] } ]},
		 modifiedDate: 1,
		 modifiedDateTime: { $add: [new Date(ISODate('0001-01-01 00:00:00')), {$divide: [{ $arrayElemAt: [ "$modifiedDate", 0 ] }, 10000] } ]},
      }
   }
])