Using Node.Js (Express) and MySQL to Insert for a Timestamp

Using Node.js (Express) and mySQL to INSERT for a TIMESTAMP?

The native JS way can be found in the answers here.

Personally, I use Moment.js for anything that deals with dates.

moment().utc().format('hh:mm:ss')

NOTE: I got confused with your question. You asked for "CURRENT_TIME" but your format is TIMESTAMP. MySQL's TIME and TIMESTAMP types are different. TIMESTAMP contains both the date and time and the default value function for that is "CURRENT_TIMESTAMP". I'm assuming you're referring to the TIME type.

mysql timestamp now() is not working with nodejs

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})

Save DateTime mysql with nodejs

With the help of @tadman, it's works

created = new Date();
connection.query('UPDATE prof_voto_foto SET punteggio = ' + connection.escape(data.voto) + ', created = ' + connection.escape(created) + ' WHERE profilo_id = ' + connection.escape(profilo) + ' AND foto_id = ' + connection.escape(data.id_foto_votata), function(error, rows) {

NodeJs and MySQL timestamp

you can also create table like

CREATE TABLE `students` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`first_name` varchar(128) NOT NULL DEFAULT '',
`is_checkout` boolean NOT NULL DEFAULT FALSE,
`last_name` varchar(128) NOT NULL DEFAULT '',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

When user check in you only create one row, current_timestamp of mysql will record current date time
and when user check out you find students by id where student.is_checkout = false and update column is_checkout to true and at that time updated_at will be record current timestamp of checkout date

update node.js express api every minute by the current timestamp

I have not worked with mysql so will assume your connection code and query works.

You need to update the module level stamp variable in your setInterval so the value is cached and set when users hit your /stats route. You were never updating the value previously.

function updateStamp() {
stamp = moment().format("YYYY/MM/DD HH:mm:00");
}

updateStamp();

setInterval(() => {
updateStamp();
}, 10000);

When a user then hits the route: '/stats' you will query the database using the stamp and return the json results.

See below for an example:

const express = require('express');
const cors = require('cors');
const mysql = require('mysql');
const moment = require('moment');

let stamp = null;

// db connection code
const config = {
host: "xxx",
user: "xxx",
password: "xxx",
database: "xxx",
timezone: '+5:00'
};

let connection = mysql.createConnection(config);

function updateStamp() {
stamp = moment().format("YYYY/MM/DD HH:mm:00");
}

updateStamp();

setInterval(() => {
updateStamp();
}, 10000);

// express stuff
const app = express();

app.use(cors());

app.get('/', (req, res) => {
res.send('hello from monitoring app')
});

app.get('/stats', async (req, res) => {
const SELECT_ALL = 'SELECT sum(xxx as xxx, time, lat, lon FROM xxx where time = ' + '"' + stamp + '"' + ' group by time, lat, lon';

connection.query(SELECT_ALL, (err, results) => {
if (err) {
console.error(err);

return res.send(err)
} else {
return res.json({
data: results
})
}
});
});

app.listen(4500, () => {
console.log(`products server is listening port 4500`)
});

I highly suggest you add additional logging and breakpoints to help analyze the code.

Inserting data using SET and NOW() with node.js to mySQL Db

I think it is better to use moment.js.

You can use Date.now which returns current timestamp(milliseconds), so make sure to format it. The code is as follows:

var a = moment(Date.now()).format('YYYY-MM-DD HH:mm:ss');

Please let me know if this works out

How to insert date into MySQL DATETIME column from Angular using Node.js?

To ensure consistency, it is helpful to store all dates in the UTC timezone.

Step 1: Convert JavaScript Date to ISO (UTC timezone)

const isoDateString: string = datePickerDate.toISOString();

This also enables sending the date via JSON to the server.

Step 2: Ensure that MySQL timezone is UTC

cursor.execute("SET time_zone = '+00:00'")

Step 3: Format date for MySQL insertion

On the Node.js server, parse the ISO date string (from step 1) and format as:

'YYYY-MM-DD HH:MM:SS'

const isoDate = new Date(isoDateString);
const mySQLDateString = isoDate.toJSON().slice(0, 19).replace('T', ' ');

MySQL Documentation

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'
format. A “relaxed” syntax is permitted here, too: Any punctuation
character may be used as the delimiter between date parts or time
parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45',
'2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.

The only delimiter recognized between a date and time part and a
fractional seconds part is the decimal point.

The date and time parts can be separated by T rather than a space. For
example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent.

As a string with no delimiters in either 'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the string makes sense as a date.
For example, '20070523091528' and '070523091528' are interpreted as
'2007-05-23 09:15:28', but '071122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00 00:00:00'.

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided
that the number makes sense as a date. For example, 19830905132800 and
830905132800 are interpreted as '1983-09-05 13:28:00'.



Related Topics



Leave a reply



Submit