MongoDB batch converts timestamps into common date format sample code

  • 2020-09-28 09:14:25
  • OfStack

preface

A timestamp (timestamp), usually a sequence of 1 characters, identifies the time of a particular moment. This article will cover MongoDB's bulk conversion of timestamps into a common date format in detail, without further elaboration. Let's start with the details

1. MONGODB traversal script provided by the official website:

[

Official document address: https: / / docs mongodb. org/manual/tutorial/remove documents /
> var arr = ["ab","cd","ef"]
> var show = function(value,index,ar){ print(value) }
> arr.forEach(show)
ab
cd
ef

]

2, MONGODB's fuzzy query

Example of MongoDB fuzzy query method:

Fuzzy query: tname contains a keyword test '


cd /opt/soft/mongodb/bin
./mongo --host 192.168.0.1 --port 17017 test
db.test_info.find({"tname": {$regex: ' test ', $options:'i'}}) 
db.test_info.find({"tname": {$regex:/ test .*/i}}) 

The data records of all timestamps need to be found. Since timestamps usually start with a number, and in recent years are 14XXX, the regular expression search begins with 14, which is implemented as follows:


mongos> use pos
switched to db pos
mongos> 
db.paymentinfo.find({"paymentTime": {$regex: '14', $options:'i'}}).count();
mongos> db.paymentinfo.find({"paymentTime": {$regex: '144', $options:'i'}}).count();
1995
mongos> 

There are 1,995 records, which is quite a lot and needs to be processed.

Establish an ergodic function:


db.cms_Content.find({"userId":"444333f107624489bae28140d1970bbc"}).forEach(function(x) {
 if(x.title&&x.fileName){
 print(x.contentId);
db.cms_Content.update({"contentId":x.contentId},{"$set":{"title":x.fileName}});
 }
})

PS: Proof does not work. Effect 1

3. First, the expansion data of TXNTYPE 1 in part 1 is deleted

Delete the queried collection data


db.paymentinfo.remove( {"txnType": {$regex: '1', $options:'i'}}, 300 );

Delete all the queried records:


db.paymentinfo.remove( {"txnType": {$regex: '1', $options:'i'}});

Delete the data record for paymentTime=0


db.paymentinfo.remove( { paymentTime : "0" } )

4. Remaining problems need to be solved

for (var i = 0, len = 3; i < len; i++) {var child=dschilds[i]; var id=child._id; printjson((id)); var paymentTime=child.paymentTime; printjson(paymentTime)}  A problem expected to be resolved by batch modifying validation error date data


 db.paymentinfo.update({"_id": ObjectId("55d56fdbe4b0c1f89b5356ae")},{$set:{"paymentTime" : "14400511608049527"}},true);
 var ds= db.paymentinfo.find({"paymentTime": {$regex: '144', $options:'i'}});
 for (var i = 0, len = 1; i < len; i++) {
 var child=dschilds[i]; 
 var id=child._id; 
 printjson(id);
 var paymentTime=child.paymentTime; 
 var datestr=paymentTime 
 # The problem here is that the date is a timestamp, for example 1440560826340 The mode, please under, in mongodb shell How do I turn a timestamp into a string '2015-12-15 12:34:16' What about a date string like this? 
 db.paymentinfo.update({"_id": id},{$set:{"paymentTime" :datestr}},true);
 db.paymentinfo.find({"_id": id});
 }
 db.paymentinfo.find({"_id":ObjectId("55dd36dc45ce9e75b91eb340")}).forEach(function (a) { a["paymentTime"] = new Date(parseInt(paymentTime) * 1000).toLocaleString().replace(/:\d{1,2}$/,' '); printjson(a) });

It doesn't solve my problem, so the train of thought stops, toLocaleString() I get the date format string for GST, not the number of date formats I need for the ES81en-ES82en-ES83en hh:mm:ss structure

5. Find a breakthrough and use JAVASCRIPT

mongodb website has been an error prompt with specifications, mongodb shell inside can be called JavaScript script, so directly inside the window to write js script to implement ok, then prepared to take 1 the data to verify whether it is right, the result is successful, verify the script is as follows: 1 � separate collection data, the timestamp into the date string:


db.paymentinfo.find({"_id":ObjectId("55d56cbbe4b0c1f89b5356a4")}).forEach(function (a) { 
 # This function is filled in front of the units digit of month, day, hour and second 0 Operation of the 
 function tran_val(val){
 if(parseInt(val)<10){
 val="0" +val;
 }
 return val;
 }
 #  Here is the paymentTime For the time stamp 
 var datenew = new Date(parseInt(paymentTime)); 
 #  Get year, month and day 
 var year=datenew.getFullYear(); 
 var month=tran_val(datenew.getMonth()+1);
 var date=tran_val(datenew.getDate()); 
 #  Get time minutes and seconds 
 var hour=tran_val(datenew.getHours());
 var minute=tran_val(datenew.getMinutes()); 
 var second=tran_val(datenew.getSeconds()); 
 #  Assemble into a standard date format yyyy-mm-dd hh:mm:ss 
 var datastr=year+"-"+month+"-"+date+" "+hour+":"+minute+":"+second; 
 a["paymentTime"]=datastr
 print(paymentTime); 
 printjson(a) }
 );

The above example shows that the timestamp to date format conversion can be achieved directly using the js script, so the for cycle batch modification starts below:


 db.paymentinfo.update({"_id": ObjectId("55d56fdbe4b0c1f89b5356ae")},{$set:{"paymentTime" : "14400511608049527"}},true);
 #  Operate by iterating through groups of Numbers 144 The timestamp of the beginning 
 var ds= db.paymentinfo.find({"paymentTime": {$regex: '144', $options:'i'}});
 var dschilds=ds.toArray();
 for (var i = 0;i <dschilds.length ; i++) {
 var child=dschilds[i]; 
 var id=child._id; 
 var paymentTime=child.paymentTime;
 print(paymentTime);
 function tran_val(val){
  if(parseInt(val)<10){
  val="0" +val;
  }
  return val;
 }
 var datenew = new Date(parseInt(paymentTime)); 
 var year=datenew.getFullYear(); 
 var month=tran_val(datenew.getMonth()+1);
 var date=tran_val(datenew.getDate()); 
 var hour=tran_val(datenew.getHours());
 var minute=tran_val(datenew.getMinutes()); 
 var second=tran_val(datenew.getSeconds());
 var datestr=year+"-"+month+"-"+date+" "+hour+":"+minute+":"+second; 
 #  This is where the modification begins 
 db.paymentinfo.update({"_id": id},{$set:{"paymentTime" :datestr}},true);
 db.paymentinfo.find({"_id": id});
 }
 #  Operate by iterating through groups of Numbers 145 The timestamp of the beginning 
 var ds= db.paymentinfo.find({"paymentTime": {$regex: '145', $options:'i'}});
 var dschilds=ds.toArray();
 for (var i = 0;i <dschilds.length ; i++) {
 var child=dschilds[i]; 
 var id=child._id; 
 var paymentTime=child.paymentTime;
 print(paymentTime);
 function tran_val(val){
  if(parseInt(val)<10){
  val="0" +val;
  }
  return val;
 }
 var datenew = new Date(parseInt(paymentTime)); 
 var year=datenew.getFullYear(); 
 var month=tran_val(datenew.getMonth()+1);
 var date=tran_val(datenew.getDate()); 
 var hour=tran_val(datenew.getHours());
 var minute=tran_val(datenew.getMinutes()); 
 var second=tran_val(datenew.getSeconds());
 var datestr=year+"-"+month+"-"+date+" "+hour+":"+minute+":"+second; 
 db.paymentinfo.update({"_id": id},{$set:{"paymentTime" :datestr}},true);
 db.paymentinfo.find({"_id": id});
 } 

6. When encountering a new problem, unify the date format of 1 and change the slash to a horizontal bar

The batch modifier date slash becomes a horizontal bar


 var ds= db.paymentinfo.find({"paymentTime": {$regex: '/', $options:'i'}});
 var dschilds=ds.toArray();
 for (var i = 0;i <dschilds.length; i++) {
 var child=dschilds[i]; 
 var id=child._id;
 var paymentTime=child.paymentTime;
 var paymentTime2=paymentTime.replace(/\//g,"-");
 db.paymentinfo.update({"_id": id},{$set:{"paymentTime" :paymentTime2}},true);
 print(paymentTime);print(paymentTime2);
 db.paymentinfo.find({"_id": id});
 } 

mongos> use pos
switched to db pos
mongos> 
db.paymentinfo.find({"paymentTime": {$regex: '14', $options:'i'}}).count();
mongos> db.paymentinfo.find({"paymentTime": {$regex: '144', $options:'i'}}).count();
1995
mongos> 
0

OK, here is a successful solution

conclusion


Related articles: