MYSQL SELECT datetime returns wrong date

Question

I'm working on a project about vacations and I'm facing weird issue, I'm using NodeJS for server side and React for Client side, after I'm adding new vacation to the database it stores it perfectly with that value at MYSQL (I'm using PhpMyAdmin currently), I checked the timezone at the PhpMyAdmin and It's +02:00 which is correct.

Here is the value at the MYSQL database :

vacationId: 12
description: D2
destination: D
flightDate: 2020-07-20 00:00:00
returnDate: 2020-07-21 00:00:00
price: 2

while the dates that I'm getting at the client side is:

Flight date: 2020-07-19T21:00:00.000Z
Return date: 2020-07-20T21:00:00.000Z

Here is how I add the vacation:

let [flightDate, setFlightDateState] = React.useState(new Date());
let [returnDate, setReturnDateState] = React.useState(new Date());

const setFlightDate = (args: ChangeEvent<HTMLInputElement>) => {
    flightDate = new Date(args.target.value);
    setFlightDateState(flightDate)
}
const setReturnDate = (args: ChangeEvent<HTMLInputElement>) => {
    returnDate = new Date(args.target.value);
    console.log(returnDate);
    setReturnDateState(returnDate)
}

let vacationToAdd = new VacationsModel(null, description, destination, flightDate, returnDate, price);
axios.post<VacationsModel>("http://localhost:3000/api/vacations/addVacation", vacationToAdd);

Here is the logic of getting the vacations at the server side:

async function getAllVacationsAsync() {
    const sql = "SELECT * FROM vacationslist";
    const vacations = await dal.executeAsync(sql);
    return vacations ;
}

Here is the controller of getting the vacations at the server side:

router.get("/", async (request, response) => { 
try {
    const vacations = await vacationsLogic.getAllVacationsAsync();
    response.json(vacations);
}
catch (err) {
    response.status(500).send(err.message);
}
});

you have to "correct" the timezone offset https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getTimezoneOffset — nbk, Jul 19 '20 at 21:19

score 0 · Answer 1 · answered Jul 19 '20 at 20:36

0

You could convert the date. Please see this Convert an ISO date to the date format yyyy-mm-dd in JavaScript

answered Jul 19 '20 at 20:36

Htet Phyo Naing

464
7
20

That's not the issue, I can split with ("T") and it will be even easier than the solutions that people wrote there, I'm having different dates on MYSQL and Client side. – Daniel Jul 19 '20 at 20:44

score 0 · Answer 2 · answered Jan 03 '23 at 20:34

You can specify the timezone "+00:00" on the createConnection function

mysql.createConnection({
        host: 'your host',
        database: 'your database',
        user: 'your user',
        password: 'your password',
        timezone : "+00:00"
    });

MYSQL SELECT datetime returns wrong date

2 Answers2