Compare MySQL Timestamp with NodeJS

Question

I am trying to compare timestamp of MySQL with current timestamp on NodeJS.

I am adding the timestamp in this way to MySQL:

var updateQuery = "UPDATE user SET last_pub = ? + INTERVAL 5 HOUR WHERE uid = '" + uid + "'";

var mysqlTimestamp = moment(Date.now()).format('YYYY-MM-DD HH:mm:ss');
return req.dbConnection.query(updateQuery, mysqlTimestamp) // I call this to run the query

I compare it in this way:

function checkTime() {
 return req.dbConnection.query(checkUser)
   .then(([rows, fields]) => {
            if (rows != "") {
                var rowResult = JSON.parse(JSON.stringify(rows[0]));
                var mysqlTimestamp = moment(Date.now()).format('YYYY-MM-DD HH:mm:ss');
                console.log(rowResult.last_pub)
                console.log(mysqlTimestamp)
                if (rowResult.last_pub > mysqlTimestamp) {
                    var dict = {}
                    dict.response = "wait"
                    console.log("hour not passed");
                    req.dbConnection.end()
                    console.log(JSON.stringify({"status": 200, "error": null, "response": dict}))
                    return res.send(JSON.stringify({"status": 200, "error": null, "response": dict}));
                }
                else {
                    console.log("done")
                }
            }
            else {
                console.log("Error - user not exist")
            }
        }).catch((error) => console.log(error));
}

When I try to print the two timestamp I got this two:

2019-01-03T04:02:00.000Z (MySQL result)
2019-01-03 00:02:07 +01:00 (moment result)

The problem is that the hour is everytimes "hour not passed" even if the last_pub is < of mysqlTimestamp

I have done some test and when I do the check with rowResult.last_pub > mysqlTimestamp its not checking the hour but its checking the day. Because If I change the hour its always "hour not passed" but If I change the day it print "done". I have to do the check with hour too

N. = All my timestamp is in UTC (System and MySQL) and the field’s type of time where we are working is set to TIMESTAMP

When it comes to timestamps you would be better using UNIX Timestamp. Also make sure to have everything in UTC time — Carlos Alves Jorge
– Carlos Alves Jorge, Commented Jan 3, 2019 at 11:01
Why are you getting the current time using Node.js? Can't you get it from the database as suggested by @Pe46dro? — Kaos
– Kaos, Commented Jan 3, 2019 at 13:12

Mouradif · Accepted Answer · 2020-05-22 14:38:19Z

2

You are comparing two strings. Try replacing this :

            var rowResult = JSON.parse(JSON.stringify(rows[0]));
            var mysqlTimestamp = moment(Date.now()).format('YYYY-MM-DD HH:mm:ss');
            console.log(rowResult.last_pub)
            console.log(mysqlTimestamp)
            if (rowResult.last_pub > mysqlTimestamp) {

with this :

            var rowResult = JSON.parse(JSON.stringify(rows[0]));
            var nodeTime = Date.now();
            var mysqlTime = new Date(rowResult.last_pub).getTime();
            if (mysqlTime > nodetime) {

edited May 22, 2020 at 14:38

answered Jan 3, 2019 at 11:23

Mouradif

2,7342 gold badges26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pe46dro · Accepted Answer · 2019-01-03 13:26:27Z

1

I think you should generate timestamp in MySQL with the function unix_timestamp. Then you should use MySQL perform the comparison and retrive only the lines that you need.

Functions:

edited Jan 3, 2019 at 13:26

answered Jan 3, 2019 at 11:29

Pe46dro

114 bronze badges

Comments

Olantobi · Accepted Answer · 2019-01-03 12:47:09Z

0

You can either use moment functions isSameOrBefore or isSameOrAfter:

var moment1 = moment(rowResult.last_pub);
var moment2 = moment(); // Current datetime

moment1.isSameOrAfter(moment2);

moment1.isSameOrBefore(moment2);

answered Jan 3, 2019 at 12:47

Olantobi

8891 gold badge8 silver badges16 bronze badges

Collectives™ on Stack Overflow

Compare MySQL Timestamp with NodeJS

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related