How to convert timestamp filed into dd/mm/yyyy format in mongodb

[balraj s]

Below is my collection format in mongodb

db.order.insert({ "Name" : "kazara", "Code" : "123A1", "Ph" : "9090111111", "Time" : Timestamp(1421555870, 0), "orderNo" : "00000007", "OrderAmt" : 122, "ItemInorder" : 1, "NetorderAmt" : 122, "TaxAmt" : 0, "CollectionAmt" : 122, "Orderno" : "0010000b00330000", "CreatedDate" : Timestamp(1421556121, 1)});


 

I've one field called Time in my order collection.

I want to extrsact date in the format "dd/mm/yyyy" from the Time filed. Plese help to extract this format.

Regards,

S.balraj

[Will Berkeley]

Store dates as the BSON date type and not as timestamps. The timestamp type is meant for MongoDB's internal use.

{

    "Name" : "kazara",

    "Code" : "123A1",

    "Ph" : "9090111111",

    "Time" : ISODate("2015-02-13T18:58:56.267Z"),

    "orderNo" : "00000007",

    "OrderAmt" : 122,

    "ItemInorder" : 1,

    "NetorderAmt" : 122,

    "TaxAmt" : 0,

    "CollectionAmt" : 122,

    "Orderno" : "0010000b00330000",

    "CreatedDate" : ISODate("2015-02-13T18:58:56.267Z")

}

When you retrieve the document, use a library to convert the Date object into dd/mm/yyyy format. moment.js is a popular library for converting date formats.

-Will

[Asya Kamsky]

While I agree with Will and Andy that the dates should be stored as ISODate() type to begin with, you can certainly extract ISODate from Timestamp or a string of "dd/mm/yyyy" from it using aggregation framework.

See example code here:  http://www.kamsky.org/stupid-tricks-with-mongodb/yet-another-way-to-group-by-dates-in-mongodb and note that $year, $month and other date manipulation operators in agg framework work on Timestamps as well as ISODate type fields.

I.e.

db.order.aggregate({$project:{date:{year:{$year:"$Time"},month:{$month:"$Time"},date:{$dayOfMonth:"$Time"}}}})

{ "_id" : ObjectId("54de670484fec29e87965927"), "date" : { "year" : 2015, "month" : 1, "date" : 18 } }

It's not quite strings, and agg framework lacks number to string convert functions, but it's pretty close and you can see other ways of manipulating dates on the same blog - there are ways to do these conversions.

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/b29409e3-61e9-4f25-9a01-4d2a3b10a71e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

{ "name" : "Asya Kamsky",
  "place" : [ "New York", "Palo Alto", "Everywhere else" ],
  "email" : "as...@mongodb.com",
  "blog" : "http://www.askasya.com/",
  "twitter": "@asya999" }

[Asya Kamsky]

Actually, it turns out you can implicitly convert from numbers (year, month, day of month) to strings via $substr

db.order.aggregate({$project:

                {date:{$concat:[

                    {$substr:[{$year:"$CreatedDate"},0,4]},

                    "/",

                    {$substr:[{$month:"$CreatedDate"},0,4]},

                    "/",

                    $substr:[{$dayOfMonth:"$CreatedDate"},0,4]} 

                ]}}

})

{ "_id" : ObjectId("54de670484fec29e87965927"), "date" : "2015/1/18" }

[Steve Schlotter]

I have a spent the last few hours looking for a way to do this, Asya's last post is the most concise I have found.  Hopefully MongoDB will someday add some sort of $date operator that would return a string with a user friendly date format, in the mean time use Asya's way.    

[Asya Kamsky]

Steve:

> Hopefully MongoDB will someday add some sort of $date operator that would return a string with a user friendly date format

Actually that was added in 3.0 - it has a $dateToString operator in aggregation framework that works with dates or ts:

db.order.aggregate({$project:{date:{$dateToString:{format:"%Y-%m-%d",date:"$CreatedDate"}}}})

{ "_id" : ObjectId("54de670484fec29e87965927"), "date" : "2015-01-18" }

It takes format string and date - I didn't mention it since the OP most certainly is still on an older version and the method I showed works in 2.6 (but it also works in 3.0).   Obviously this way is "cleaner" so it will be preferable in 3.0 and later.

The ticket that added this operator was https://jira.mongodb.org/browse/SERVER-11118 and the docs added info about it in https://jira.mongodb.org/browse/DOCS-3823

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/774c73ec-d5f2-4a2c-8b22-4fdae754bd17%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[inte...@googlemail.com]

Hi Asya,

I came upon this post during my research about a problem with the aggregation operator $dateToString and my local timezone setting.

The app is written in PHP and with the MongoDate class everything is fine. The php driver handles the transformation between local timezone and UTC.

But as soon as $dateToString operator comes into play, everything is getting a little bit tricky. 

I have the follwing document:

{

"timestamp" : NumberLong(1443650693),

"created_at" : ISODate("2015-09-30T22:04:53.973+0000")

}

In my local timezone the document was created at the 2015-10-01. If I aggregate with the dateToString operator, the UTC date will be returned (2015-09-30) and this is a string and I have no chance to reformat this into my local timezone.

Is there a workaround for this?

Thanks in advance!

Marcus

[Asya Kamsky]

Since time zones is a very different topic it probably would be better to start a new thread, but in a nutshell, the way you would handle this would be by doing date arithmetic to convert the time *before* applying dateToString.

So in aggregation, instead of doing

{$project:{date:{$dateToString:{format:"%Y-%m-%d",date:"$created_at"}}}}

you would do:

{$project:{date:{$dateToString:{format:"%Y-%m-%d",date:{$add:["$created_at",X*60*60*1000]}}}}}

where X is the number of hours you are ahead of UTC (date addition in aggregation takes milliseconds).

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.

Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/b4632f20-86eb-4a76-ba65-0de038fbbeaf%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers

[inte...@googlemail.com]

Hi,

yes I thought about that, but unfortunately in Germany we have summer time (utc + 2) and winter time (utc + 1). A short example:

The last switch from summer to winter time was on 25.10.2015. E.g. there is a collection with page impressions. Every single impression is saved as a single document containing a created_at field with the creation time (Of course one document and increasing a field for each impression would be smarter but it's a historical thing). The user want to query the page impressions for October 2015 grouped by day.

So I have to execute two aggregations or (if that is possible but I haven't seen this before) do some if else magic within the project. Both sound a little bit awkward to me.

Another solution would be to store the local time as string as a separate field e.g. created_at_cet = "2015-10-27". Daily aggregation would be very easy but it's not very flexible if we want to group on weeks or months.

[Christian Dechery]

I found a way to do it, using the map method.

Here is how it works.

Create a function to convert the timestamp do a formated date, or take mine:

function toDateStr(ts) {

    let dataF = new Date();   dataF.setTime(ts);

    let strDataF = dataF.toLocaleString();

    return strDataF;

}

then use it in your aggregation:

db.Collection.aggregate([{ whatever you want here, be sure to return the timestamp field

}]).map( function(doc) { 

doc['date'] = toDateStr(doc.timestamp);

return doc;

})

Done!:)