Mongodb how to convert timestamp to year, month, day and date

Catalogue

• Mongodb converts timestamp to year, month, day and date
• The pitfalls of date query in MongoDB

Mongodb converts timestamp to year, month, day and date

Use the dateToString method to convert and specify the conversion date format through format

 Integer userId=aaa;
 GroupOperation groupOperation = Aggregation.group("day").sum("money").as("todayIncome").count().as("todayPayCount");
 Aggregation aggregation = Aggregation.newAggregation(
 Aggregation.match(Criteria.where("userId").is(userId)),
 project("userId","money").andExpression("{$dateToString: {date: { $add: {'$createTime', [0]} }, format: '%Y%m%d'}}"  , new Date(28800000)).as("day"),
                group Operation,
                   sort(Sort.Direction.ASC, "_id")
 );

Note:

1. Must use $dateToString: {date: { $add: date data conversion through summation. If you remove the following, a parsing error will be reported

org.springframework.data.mongodb.UncategorizedMongoDbException: Command failed with error 16006 (Location16006): ‘can’t convert from BSON type long to Date’ on server localhost:50000. The full response is {“ok “: 0.0, “errmsg”: “can’t convert from BSON type long to Date”, “code”: 16006, “codeName”: “Location16006”}; nested exception is com.mongodb.MongoCommandException: Command failed with error 16006 (Location16006): ‘can’t convert from BSON type long to Date’ on server localhost:50000. The full response is {“ok”: 0.0, “errmsg”: “can’t convert from BSON type long to Date”, “code”: 16006, “codeName”: “Location16006”}

2. The time of new Date(28800000) must be increased. The reason is that the offset of 8 time zones is added

The pitfalls of date query in MongoDB

When I was familiar with monggoDB, I encountered the problem of time query. The code is as follows:

import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.List;
 
 import com.mongodb.BasicDBObject;
 import com.mongodb.DB;
 import com.mongodb.DBCollection;
 import com.mongodb.DBCursor;
 import com.mongodb.DBObject;
 import com.mongodb.MongoClient;
 import com.mongodb.ServerAddress;
 
 /**
  * Mongo database direct connection test
  * @author fuhao
  *
  */
 public class MongDBTest {
 public static void main(String[] args) throws Exception {
 List list = new ArrayList();
 //Connect to database ip port
 list.add(new ServerAddress("10.39.XXX.XXX", 27010));
 MongoClient mOngoClient= new MongoClient(list);
 		//Name database
 DB psdoc = mongoClient.getDB("qa_db_center");
 //Indicate
 DBCollection collection=psdoc.getCollection("base_user_info");
 	    
 BasicDBObject queryObject = null;
 	    
 // Time query. The time seen by the database is not the real time. The correct time is after adding 8 hours.
 DBObject dbObject = new BasicDBObject();
 String startDate = "2018-03-29 15:59:06";
 String endDate = "2018-03-29 16:30:46";
 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 dbObject.put("$gte", sdf.parse(startDate));
 dbObject.put("$lte", sdf.parse(endDate));
 queryObject = new BasicDBObject();
 queryObject.put("create_time",dbObject);
 DBCursor find = collection.find(queryObject);
 	     
 while (find.hasNext()) {
 DBObject next = find.next();
 Object real_name = next.get("real_name");
 Object mobile = next.get("mobile");
 Object create_time = next.get("create_time");
 String str = sdf.format(create_time);
 System.out.println(real_name +"====="+mobile +"====="+str);
 }
 System.out.println("End");
 	    
 }
 }

The default page of the request page https://blog.csdn.net/qq_27292113/article/details/91876121 [Title]: Pitfalls of date query in MongoDB_Tianmaxingkong-‘s blog-CSDN blog_mongodb query date [Content] :

When I was familiar with monggoDB, I encountered the problem of time query. The code is as follows:

In the above code, the query time according to the mysql process should query the data in the interval from 2018-03-29 15:59:06 to 2018-03-29 16:30:46, but mongoDB is different, because in mongo The date type is stored in UTC (Coordinated Universal Time), which is equivalent to GMT (Greenwich Mean Time) time. The system time uses GMT+0800, which is exactly 8 hours apart. That is, the time type value inserted using java code will be subtracted by 8 hours. This pit is quite big and it is easy to get into trouble if you don’t pay attention.

Show the comparative data for easier understanding:

The circle above is the query condition, and the corresponding data in the database is 2018-03-29T08:30:36.310Z, as shown below, but in Java, if you write 2018-03-29 08:30:36, this time will definitely not be checked. to data

The comparison shows that the time seen in the database is 8 hours different from the actual time, but the result time of the query will still be converted back (there is basically no problem if the query is not based on time).

Record the execution statement of query interval time in mongoDB:

db.getCollection('base_user_info').find({"create_time":{"$gte":ISODate("2018-03-29 07:59:06"),"  $lte":ISODate("2018-03-29 08:30:46")}});

base_user_info: table name create_time: field name

Comparison symbol correspondence list

• $gt ——– greater than >
• $gte ——— gt equal >=
• $lt ——– less than <
• $lte ——— lt equal <=
• $ne ———– not equal !=
• $eq ——– equal =

The above is my personal experience, I hope it can give you a reference, and I hope you will support me a lot.

Original address: https://blog.csdn.net/lMasterSparkl/article/details/109679841