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
How to Get the Numeric Part from a String Using T-Sql
Getting Two Counts and Then Dividing Them
Column Is of Type Timestamp Without Time Zone But Expression Is of Type Character
Exclude a Column Using Select * [Except Columna] from Tablea
How to Check If More Than One Column Is Not Null Per Record
Check If a Row Exists, Otherwise Insert
Sql Server How to Find the Customers Who Have Bought a Product from Each Store
Select Rows Within Last Complete Minute
Sql Query for Values Consisting of Only a Specific Character
Converting to Timestamp With Time Zone Failed on Athena
Two Rows With the Same Id and Two Different Values, Getting the Second Value into Another Column
Left Join Without Duplicate Rows from Left Table
Could Not Load File or Assembly Error in .Net Standard 2.0 Class Library
The Network Adapter Could Not Establish the Connection in SQL Developer
Laravel Eloquent Mutiple Counts With Different Conditions in One Query