I've faced one strange problem: if I insert or update dates, they are always by 1 day wrong (earlier) than the dates I place in my statements... I tried different solutions (adding to query statenents time, time zones, changing date format etc), but nothing influences the result. Each time I fulfill updates, the date shifts by -1 day! I'm using Node JS server together with mysql2 module; as for the MySQL server, I tried a local server of Xampp and a free test server by remotemysql.com, and on both I have this problem.
To make a situating a bit clearer, I include console logs (from Node JS) of query statements and database server results:
Update query statement:
UPDATE Employees SET fullName = 'Some Random Dude', phone = '931112233',
gender = 'm', birthDate = '1992-02-18', workStartDate = '2021-01-14', numOfChildren = '0', workType =
'departmentEmployee', department = '', workLoad = '1.25', currentStatus
= 'worker', extraInfo = 'some info' WHERE id = 1
Results:
[
TextRow {
id: 1,
fullName: 'Some Random Dude',
phone: 931112233,
gender: 'm',
birthDate: 1992-02-17T22:00:00.000Z, // 1992-02-17 instead of 1992-02-18
numOfChildren: 0,
workStartDate: 2021-01-13T22:00:00.000Z, // 2021-01-13 instead of 2021-01-14
workType: 'departmentEmployee',
department: '',
workLoad: '1.25',
currentStatus: 'worker',
extraInfo: 'some info'
}
]
In this example, both workStartDate and birthDate values get distorted by -1 day, and when I perform a search (with a select statement), I also get wrong results... How to solve this?
UPD: the table was created by this statement:
CREATE TABLE IF NOT EXISTS Employees (id int NOT NULL AUTO_INCREMENT, fullName varchar(100), phone int, gender char(1), birthDate date, numOfChildren tinyint, workStartDate date, workType varchar(20), department varchar(80), workLoad dec(3,2), currentStatus varchar(10), extraInfo varchar(255), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
UPD: my Node js code:
a file 'MySQL.js':
const mysql = require('mysql2'); function createConnection(withDatabase) { return new Promise((res, rej) => { let connection = mysql.createConnection({ host: "127.0.0.1", port: "3306", user: "root", database: withDatabase ? "universityDB" : undefined, password: "", }); connection.connect(err => { if (err) { console.log(err); rej({error: err, logInfo: 'невдала спроба з\'єднання з MySQL сервером'}); } else { console.log("З'єднання з MySQL сервеом було успішно вастановлено."); res(connection); } }); }); } function query(withDataBase) { return function (...args) { return createConnection(withDataBase).then(connection => { return new Promise((res, rej) => { connection.query(...args, (err, results) => { if (err) { console.log(err); rej({error: err, logInfo: 'хибний запит на MySQL сервер'}); } else { //console.log(results) res(results); } }); }).then(results => { connection.end(); return results; }); }); } } module.exports.query = query(true); module.exports.createDB = () => query(false)('CREATE DATABASE IF NOT EXISTS universityDB');
extracts from the 'dbQueries.js' file:
function editEmployee({ id, updates }) { const { fullName, phone, gender, birthDate, workStartDate, numOfChildren, workType, department, workLoad, currentStatus, extraInfo } = updates; const statement = `UPDATE Employees SET fullName = '${fullName}', phone = '${phone}', gender = '${gender}', birthDate = '${birthDate}', workStartDate = '${workStartDate}', numOfChildren = '${numOfChildren}', workType = '${workType}', department = '${department}', workLoad = '${workLoad}', currentStatus = '${currentStatus}', extraInfo = '${extraInfo}' WHERE id = ${id}`; console.log(statement) return query(statement) .catch(() => createEmployeeTableIfNotExists() .then(() => query(statement))) }
extracts from the app.js file: const q = require('./dbQueries.js');
app.patch('/employeesPage', (request, response) => { console.log(request.body); q.editEmployee(request.body) .then(result => {response.status(201).json(result)}) .catch(err => { response.status(418).json(err); }); });