Node.JS Trying to create a process that counts records from a MariaDB and stores the result in another table

[Malcolm]

Howdy folks,

I'm building a process in my Node server that counts each record at the end of every day and stores them in another table. I'll use this table later to create charts, etc.

I want to ensure that it only counts the data within that particular day. So far, I have a cron that runs a function and queries the database, asking for the counts from a particular table (in the example below, I'm using the bounces table).

My question is, can I query the database table for the records added in the last 24 hours? (note: I'm storing UNIX timestamps).

const job = new CronJob(
'* * * * *',
function(){
  calculateData();
},
null,
true,
'America/Los_Angeles'

);

async function calculateData(){
  const con = await mysqlConnect();
  const sql = "SELECT COUNT(*), created FROM bounces GROUP BY created";

  return new Promise((resolve, reject) => {
    con.query(sql, function (err, result) {
      con.release();

      if (err) {
        console.error("Error queying database:", err);
        reject(err);
      } else {

        result.forEach((rawDataPacket) => {
          console.log(rawDataPacket, "worked")
        });

        resolve(result);
      }
    });
  });
  console.log("hey")
}

 

[JosiahMaybe]

Okay, I do not understand all that but I think you should have your SQL stuff like this: "SELECT COUNT() created FROM bounces WHERE now - created <= SECONDS_IN_DAY GROUP BY created", and you can even drop first created I think. I think yes it can be done. However you are getting Unix timestamps, insert a gotten now in Unix timestamps and it should work. I do not know Node.js but I know there is a way. If all else fails, insert that Unix now where "now" is in previous SQL and also, substitute your value for SECONDS_IN_DAY. What I mean is like sql="SELECT COUNT(), created FROM bounces WHERE "+unix_now+" - created <= SECONDS GROUP BY created";. I cannot get * in COUNT but you get idea. X E.

 

[menator01]

I was playing around with this today and found that something like this may work. Uses UNIX_TIMESTAMP
I take the time of script execution - 86400 secounds (24 hours) and pull results in between that.
Hope it helps.

var mariadb = require('mysql2');

const pool = mariadb.createPool({
    host: 'localhost',
    user: 'auser',
    password: 'mypass',
    database: 'play'
});


pool.query('select count(*) from charts where stamp <= UNIX_TIMESTAMP() and stamp > UNIX_TIMESTAMP()-86400', (err, results) => {
    if (err) {
        console.error('bad query');
        return;
    }
  
    console.log(results)
    // for (let i=0; i<results.length; i++) {
    //     console.log(results[i]);
    // }
    pool.end();
})

output

[ { 'count(*)': 2 } ]