0

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:

  1. 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');
    
  2. 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)))
             }
    
  3. 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);
                 });
             });
    
Roman Karagodin
  • 740
  • 2
  • 11
  • 16
  • 1
    Can you include your table schema? What is the field type for these date fields? This appears to be a timezone issue, because you're inserting `1992-02-17`, but in the database it's -2 hours of that. If you don't care about the time for this field, make sure to set its field type as `DATE` – WOUNDEDStevenJones Jan 14 '21 at 21:58
  • you have another timezone at your server – nbk Jan 14 '21 at 22:00
  • yeah, I've already included the table scheme, thanks for a hint – Roman Karagodin Jan 14 '21 at 22:02
  • 1
    If you take Node out of the equation, do you still have this issue? For example, if you run the `insert` query _directly in MySQL_, and then run a `select` query _directly in MySQL_, do those results look correct? When selecting a field that is a `date` type, MySQL should return it in the format `1992-02-17`, not `1992-02-17T22:00:00.000Z`, so I suspect Node is adjusting the data before outputting it. – WOUNDEDStevenJones Jan 14 '21 at 22:05
  • If I perform these statements in PHPMyAdmin, everything is correct. Also, if I perform update statements via Node, in PHPMyAdmin the results are displayed correctly. – Roman Karagodin Jan 14 '21 at 22:08
  • 1
    It sounds like the issue isn't in MySQL, it's with Node. Please include any relevant Node config/code in order to debug this further. Or see https://stackoverflow.com/questions/23571110/nodejs-responded-mysql-timezone-is-different-when-i-fetch-directly-from-mysql for other suggestions (specifically a combination of https://stackoverflow.com/a/36246746/1499877 and https://stackoverflow.com/a/23571551/1499877 might fix it). – WOUNDEDStevenJones Jan 14 '21 at 22:10
  • Lots of thanks! After adding the line "timezone: 'utc'" to the object-parameter of mysql2.createConnection({ //here }) , the problem is gone. – Roman Karagodin Jan 14 '21 at 23:10

0 Answers0