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