mysql timestamp now() is not working with nodejs

Question

I am trying to insert a row into mysql table using nodejs. I am trying to update a column creation_date(timestamp) by sending its value as now() in nodejs, but it is giving an error saying that "now" is not defined. When I am executing the same sql query in mysql directly, it is working. I do not know where the problem is. Can somebody please help me here... This is the nodejs code where I am inserting values into the table in mysql..

var sql = `INSERT INTO sales
    (
        user_id, name, tagline, start_date, 
        start_time, end_date, reg_start, 
        reg_end, descr, creation_date
    ) VALUES (
        ?, ?, ?, ?,
        ?, ?, ?,
        ?, ?, ?
    )`

pool.query(sql, [
    id, req.body.sale_name, req.body.tag, req.body.start,
    req.body.stime, req.body.end, req.body.reg_start,
    req.body.start, req.body.descr, now()
], function (err, result) { //further code})

The error which I am getting is : ReferenceError: now is not defined

few questions :: is your client and db server running in same timezone? if not which datetime you want to store in db? if you want to store the db time you can ignore this feild in query use NOW() as default value in sql. — Muhammad Saifuddin
– Muhammad Saifuddin, Commented Dec 20, 2017 at 9:54
you are using node js so use new Date() instead now().now() works when you insert data using mysql — Chandrakant Thakkar
– Chandrakant Thakkar, Commented Dec 20, 2017 at 10:00

Камилов Тимур · Accepted Answer · 2017-12-20 09:56:40Z

5

You can get today's date by using general JavaScript object.

var datetime = new Date();

So, I guess you should write smth like this:

pool.query(sql, [
    id, req.body.sale_name, req.body.tag, req.body.start,
    req.body.stime, req.body.end, req.body.reg_start,
    req.body.start, req.body.descr, new Date()
], function (err, result) { //further code})

answered Dec 20, 2017 at 9:56

Камилов Тимур

8982 gold badges14 silver badges30 bronze badges

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

1 Comment

nanquim Over a year ago

Date() returns the server's time. You should use Date.now(), it returns the UTC time github.com/drudge/mongoose-timestamp/issues/23#issue-51846618

Muhammad Saifuddin · Accepted Answer · 2017-12-20 10:02:47Z

1

The reason you're getting this error because now() works on MYSQL where running now() in javascript looking for function which doesn't exist in your script.

Correct way of loading date in Javascript is "Date.now();"

Reference: JavaScript now() Method

answered Dec 20, 2017 at 10:02

Muhammad Saifuddin

1,29411 silver badges29 bronze badges

Comments

bhavya_karia · Accepted Answer · 2019-04-18 10:08:57Z

TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time (that is, the current timestamp) in MySQL.

So, here you can set current timestamp without passing it from the client side. Whenever a row is inserted in the MySQL database then the current timestamp will automatically set to the TIMESTAMP/ DATETIME column.

CREATE TABLE Statement:

CREATE TABLE t1 (
  // rest of the statements
  'creation_timestamp' TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  'creation_datetime' DATETIME DEFAULT CURRENT_TIMESTAMP,
);

Now, whenever a row is inserted into the database then the creation_timestamp/creation_datetime column will be automatically set to the current timestamp.

Note:

Here, you don't need to pass any date or timestamp in the insert statement from the client side.
You can learn more about it here.

Dominik · Accepted Answer · 2017-12-20 09:54:27Z

0

I think, you have to give now() as a String like that:

  pool.query(sql, [
    id, req.body.sale_name, req.body.tag, req.body.start,
    req.body.stime, req.body.end, req.body.reg_start,
    req.body.start, req.body.descr, 'now()'
  ], function (err, result) { //further code})

answered Dec 20, 2017 at 9:54

Dominik

2997 silver badges18 bronze badges

2 Comments

Megha Verma Over a year ago

The row got inserted into the table but the value was 00-00-0000 00:00:00 got saved in the table.

user3044394 Over a year ago

@MeghaVerma that's because it's not using NOW() it's using 'NOW()'. I'm surprised it converted the string into a date and didn't just error out.`

Leonardo Jauregui · Accepted Answer · 2024-07-31 15:24:15Z

It is possible to insert a function into a data array for bulk insertion or other type of parameterized queries.

For this, the "raw" function is used. See the example where the data array is iterated and the "NOW()" function is added using the mysql.raw() function. Hope it helps!

var mysql      = require('mysql');

let connection = doConection(); // make conection

let masiveInsertDataArray = rowsDataArray.map(function(row){ 
    let timestamp = new Date(row.timestamp);
    return [
        row.name,                   // "Leonardo"
        row.otherData,              // "fullStackDev"
        row.someRelationalId,       // "18"
        mysql.raw('NOW()'),  // timestamp mysql field created_at
        mysql.raw('NOW()'),  // timestamp mysql field updated_at
    ]; 
});

let sqlStatment = `
    INSERT INTO some_table 
        (name, profession, some_relational_id, created_at, updated_at) 
    VALUES 
        ?
`;

connection.query(sqlStatement, masiveInsertDataArray, (error, result, fields)=>{
    console.log(result);
});

Collectives™ on Stack Overflow

mysql timestamp now() is not working with nodejs

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related