MySQL - Fetch date without timezone offset

Question

I'm storing Date of bith (DOB) as Date column in mySQL table.

Here's the problem now: When I insert the date-of-bith, eg: 2001/02/02, I'm able to store it without any problem. But when I select it (SELECT dob FROM table1), I'm getting result in UTC format like: 2001-02-01T18:30:00.000Z

I went on further digging to find out it's because of UTC offset.

Is there any way to return Date as stored in table without UTC offset? Date of birth(s) needs to be constant for all timezone irrespective of UTC.

Note: I don't want to change Server timezone since I do have certain column that uses time difference.

Edit: When I execute the query using MySQL workbench, I'm getting expected results but when I use node (mysqljs), I'm getting results in timezone format.

Edit: Just found out that it was a problem with mysqljs since it connects to mysql server using timezone property. (https://github.com/mysqljs/mysql#connection-options)

Thank you in advance.

Answer 1

So, It was an issue with mysqljs and I used timezone option while creating connection. mysqljs uses local as default for timezone option and setting it to Z seems to fix the issue for me.

Here's a sample:

`mysql.createPool({
  connectionLimit: mySQLConfig.connectionLimit,
  host: mySQLConfig.host,
  user: mySQLConfig.username,
  password: mySQLConfig.password,
  database: mySQLConfig.database,
  timezone: 'Z'
});`