Calculate the combined duration of online activity from two specified dates

Presented below is a sample collection:

[
  { "_id" : ObjectId("5e7b38534512e95591e14c59"), "name" : "Luna", "online" : true },
  { "_id" : ObjectId("5e7b386b4512e95591e14c5a"), "name" : "Luna", "online" : false },
  { "_id" : ObjectId("5e7b386b4512e95591e14c5b"), "name" : "Luna", "online" : true },
  { "_id" : ObjectId("5e7b4e128e6bf5694924db09"), "name" : "Luna", "online" : false },
  { "_id" : ObjectId("5e7b4ec81b36e86a1fb754bf"), "name" : "Luna", "online" : true },
  { "_id" : ObjectId("5e7b4f3b1b36e86a1fb754c0"), "name" : "Luna", "online" : false },
  { "_id" : ObjectId("5e7b4f581b36e86a1fb754c1"), "name" : "Luna", "online" : true },
  { "_id" : ObjectId("5e7b5065f5700f6bee612040"), "name" : "Luna", "online" : false },
  { "_id" : ObjectId("5e7b51a3f5700f6bee612041"), "name" : "Luna", "online" : true },
  { "_id" : ObjectId("5e7b5885f5700f6bee612042"), "name" : "Luna", "online" : false },
  { "_id" : ObjectId("5e7b588ff5700f6bee612043"), "name" : "Luna", "online" : true }
]

The objective is to create a query that matches data between two specified dates and calculates the total online time during that period, using the MongoId for timestamp reference.

I have already figured out how to query data between these dates:

var objectIdFromDate = function (date) {
    if(typeof date === 'string') date = new Date(date);
    return ObjectId( Math.floor(date.getTime() / 1000).toString(16) + "0000000000000000" );
};

db.getCollection('timeline').aggregate([
    {$match:{
        name:"Luna",
        _id:{
               $gte: objectIdFromDate("2020-03-25T10:54:35.000Z"),
               $lt: objectIdFromDate("2020-03-25T12:36:53.000Z") 
            }
    }},
    // ... ??? ...
])

The challenge lies in calculating the total online time by considering changes in online state (true=login, false=logout). The desired result document structure would be as follows:

{
  name:"Luna",
  totalTimeOnline:<datetime here>
}
javascriptmongodbmongodb-query

Answer №1

You can categorize the time spans into online and offline groups, then aggregate the values to obtain a total count.

The resulting durations are presented in HH:MM:SS format, with times measured in milliseconds.

Interestingly, I've structured my time intervals according to the Fibonacci Sequence, deviating only for 01:00 and 01:30 to maintain distinctiveness.

If the current login status is ONLINE, it signifies that the user has been OFFLINE since their last session.

const main = () => {
  let sessions = calculateSessions(logins);

  Object.keys(sessions).forEach((key) => {
    let duration = sessions[key].reduce((sum, ms) => sum + ms);
    console.log(`Time spent ${key}: ${formatTime(duration)}`);
  });
}

let logins = [
 { "id" :  1, "timestamp" : "2020-03-23T00:00:00.000Z", "online" : true  },
 { "id" :  2, "timestamp" : "2020-03-23T01:00:00.000Z", "online" : false },
 { "id" :  3, "timestamp" : "2020-03-23T01:30:00.000Z", "online" : true  },
 { "id" :  4, "timestamp" : "2020-03-23T02:00:00.000Z", "online" : false },
 { "id" :  5, "timestamp" : "2020-03-23T03:00:00.000Z", "online" : true  },
 { "id" :  6, "timestamp" : "2020-03-23T05:00:00.000Z", "online" : false },
 { "id" :  7, "timestamp" : "2020-03-23T08:00:00.000Z", "online" : true  },
 { "id" :  9, "timestamp" : "2020-03-23T13:00:00.000Z", "online" : false },
 { "id" : 10, "timestamp" : "2020-03-23T21:00:00.000Z", "online" : true  },
 { "id" : 12, "timestamp" : "2020-03-24T10:00:00.000Z", "online" : false },
 { "id" : 13, "timestamp" : "2020-03-25T07:00:00.000Z", "online" : true  }
];

// Assuming dates are pre-sorted and the online status is always staggered
const calculateSessions = (logins) => {
  return logins.reduce((sessions, login, index, all) => {
    if (index > 0) {
      let duration = new Date(login.timestamp) - new Date(all[index - 1].timestamp);
      sessions[login.online ? 'offline' : 'online'].push(duration);
    }
    return sessions;
  }, { online : [], offline : [] });
};

const formatTime = (ms) => {
  const pad = (n, z = 2) => ('00' + n).slice(-z);
  const hh = pad(ms / 3.6e6 | 0);
  const mm = pad((ms % 3.6e6) / 6e4 | 0);
  const ss = pad((ms % 6e4) / 1000 | 0);
  const mmm = pad(ms % 1000, 3);
  return `${hh}:${mm}:${ss}.${mmm}`;
};

main();
.as-console-wrapper { top: 0; max-height: 100% !important; }

Specialized scenario

const main = () => {
  let details = calculateTimeOnline('Luna', logins);

  console.log(Object.assign({}, details, {
    totalTimeOnline : formatTime(details.totalTimeOnline)
  }));
}

let logins = [
 { "id" :  1, "timestamp" : "2020-03-23T00:00:00.000Z", "online" : true  },
 { "id" :  2, "timestamp" : "2020-03-23T01:00:00.000Z", "online" : false },
 { "id" :  3, "timestamp" : "2020-03-23T01:30:00.000Z", "online" : true  },
 { "id" :  4, "timestamp" : "2020-03-23T02:00:00.000Z", "online" : false },
 { "id" :  5, "timestamp" : "2020-03-23T03:00:00.000Z", "online" : true  },
 { "id" :  6, "timestamp" : "2020-03-23T05:00:00.000Z", "online" : false },
 { "id" :  7, "timestamp" : "2020-03-23T08:00:00.000Z", "online" : true  },
 { "id" :  9, "timestamp" : "2020-03-23T13:00:00.000Z", "online" : false },
 { "id" : 10, "timestamp" : "2020-03-23T21:00:00.000Z", "online" : true  },
 { "id" : 12, "timestamp" : "2020-03-24T10:00:00.000Z", "online" : false },
 { "id" : 13, "timestamp" : "2020-03-25T07:00:00.000Z", "online" : true  }
];

// Assuming dates are pre-sorted and the online status is always staggered
const calculateTimeOnline = (user, logins) => {
  return logins.reduce((result, login, index, all) => {
    return index > 0 && !login.online
      ? Object.assign(result, {
          totalTimeOnline : result.totalTimeOnline +
            new Date(login.timestamp).getTime() -
            new Date(all[index - 1].timestamp).getTime()
        })
      : result;
  }, {
    name: user,
    totalTimeOnline: 0
  });
};

const formatTime = (ms) => {
  const pad = (n, z = 2) => ('00' + n).slice(-z);
  const hh = pad(ms / 3.6e6 | 0);
  const mm = pad((ms % 3.6e6) / 6e4 | 0);
  const ss = pad((ms % 6e4) / 1000 | 0);
  const mmm = pad(ms % 1000, 3);
  return `${hh}:${mm}:${ss}.${mmm}`;
};

main();
.as-console-wrapper { top: 0; max-height: 100% !important; }

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Glitch in the system: Customizing buttons with Google Maps API v3

With my custom buttons on Google Maps, I am able to create markers with different icons for each button. However, when I click on one button to create a marker and then click on another button to create another marker, the two buttons end up overlapping ea ...

javascripthtmlgoogle-maps-api-3

Exploring MongoDB: Leveraging the find() Method for Retrieving Elements Within an Array

Within my collection are documents that follow this structure: { 'game_name': 'soccer', 'points': [3,2,5,1,4,0] } I am looking to figure out how to retrieve the 'points' from all ...

mongodb

real-time uploading of zip files to Firebase via URL

https://i.sstatic.net/lIZip.png I am struggling to generate the same type of URL (as shown in the picture) for my file in Realtime Firebase database. Creating JavaScript code is proving difficult for me. ...

javascripturlfirebase-realtime-databasegoogle-cloud-filestore

Result of Mongodb aggregation operation

I've created a Property controller : //Retrieve Properties By Type const getPropertiesByType = async (req: Request, res: Response) => { const { cities, type } = req.query; const citiesArr = typeof cities === 'string' ? cities.spli ...

typescriptmongodbexpressmongoose

inability to conceal a two-dimensional marker array within Google Maps API v3

I need some help with my marker that refuses to hide Even after using setMap, my marker is still visible on the map Here is the error message from the console Please assist! Thank you in advance markers[i][j].setMap(null); markers.setMap(null); va ...

javascriptarraysgoogle-mapsgoogle-maps-api-3

What is the best way to showcase a QR code on a mesh using three.js?

I utilized the QRcode library available at to try and showcase it on a mesh object as a texture using the following code: var qrcode = new QRCode( "test", { text: "http://jindo.dev.naver.com/collie", width: 128, height: 128, colorDark : " ...

javascriptthree.jsqr-code

Performing an AJAX request from a secure HTTPS page to an insecure HTTP URL

Currently, I am facing a scenario where I must execute an AJAX request from an HTTPS webpage to a non-secure page on a different domain. Our CORS policy was functioning correctly prior to switching to HTTPS on our site. Are there any solutions to resolve ...

javascriptajaxhttphttpscors

dispatch email display Twitter Bootstrap notification utilizing Ajax

I'm trying to set up an email sending feature via a contact form. I want it to send the email and display a Bootstrap alert confirming that the email has been sent. Below is the HTML code: https://jsfiddle.net/6rfeas35/. Additionally, here is my PHP c ...

javascriptphpjqueryajaxtwitter-bootstrap

What is the method for shifting a value within an array using Jquery?

Looking to prioritize my name in a list of online chat members but unsure how to achieve the shortest method possible. Here is my JQuery code snippet: // Sorting Online Members List with My Name at the Top var list = [ {"userid":"19380","username":"User ...

javascriptjqueryarrays

Utilizing the power of Angular 4 in combination with mailgun

I need assistance with setting up an email form on my website using Angular 4 and Mailgun as the mail service. I have a method in my mail service file to send messages, but I keep encountering a Bad Request error stating that 'from' is not presen ...

javascriptangularmailgun

The Heroku app is having trouble communicating with the Mongo Atlas database, leading to frequent crashes

My deployed application on Heroku was working fine until I encountered an issue with the connection to my MongoDB Atlas database. Despite my attempts to resolve it, the application keeps crashing. The Heroku logs show the following: "2022-12-05T10:19: ...

javascriptmongodbmongooseherokumongodb-atlas

Interpolating backticks in Javascript allows for constructing a URL containing empty spaces

When utilizing string interpolation with backticks to construct a URL that sends data to a django endpoint, the resulting URL contains unnecessary whitespace and a new line. The problematic JavaScript code is as follows: (function (window, document, unde ...

javascriptdjangoajax

What is the best way to prevent an iFrame from causing its parent window to scroll?

I recently came across an HTML document that includes an iframe towards the bottom of its content. Specifically, the iframe is embedding a Google Spreadsheet, using the following code: <iframe id="gsheet-group" width="100%" height=& ...

javascripthtmlcssiframe

Executing a function in C# using JavaScript

Currently, I am utilizing selenium's RemoteWebDriver along with c#. At the moment, I am inserting a javascript function into the head of a page that has the following structure: "window.__webdriver_javascript_errors = []; window.onerr ...

c#javascriptseleniumremotewebdriver

Is it possible to make API calls within the componentWillMount lifecycle method in React?

I've been immersed in the world of React for the past year. The standard practice within my team is to make an API call in componentDidMount, fetch the data, and then setState after receiving the data. This ensures that the component has fully mounted ...

javascriptreactjs

Leveraging Node.js and Mongoose to implement a request.params-based find operation

I have a MongoDB database with two collections: Markers and Pois. In my schema, MarkerID is a foreign key in the Pois collection and a primary key in the Markers collection. My goal is to retrieve Pois that are associated with a specific Marker ID. I am ...

node.jsmongodbmongoose

Ensure that the "Read more" button appears on the same line as the truncated text after a specified number of lines

I am working on getting the Read more feature to appear on the same line as the truncated text after a certain number of lines. Currently, it is showing up below the truncated text. <p style={{ overflow: 'hidden', display: '-webkit-box&ap ...

javascriptreactjstypescript

Inquiring about the best method to determine the intersection point between two lines in Draw i.o

Can someone please explain how to find the coordinate point where two lines intersect in draw i.o? I am looking for a Javascript function to solve this issue and get the coordinate of the point only. For example: ...

javascripthtmlcssdraw

Show blob file as a PDF document in a popup or dialog box using HTML and TypeScript

I am currently working on integrating TypeScript and HTML to showcase the result of a webservice call as a PDF in a popup/dialog within the same page. While I can successfully open the PDF in a new tab using the window.open(url) method, I'm encounter ...

javascripthtmltypescriptpdfblob

Having difficulty accessing POST data through $.ajax request

I am currently working on a simple JavaScript code that is set up to send POST requests to my local server. The JavaScript and PHP files are both located on localhost, so I don't have to worry about any cross-site issues for now. Here is the JavaScrip ...

javascriptphpjqueryajax