CODEWITHSUNDEEP
mysqlnode.jsdatabasedatetimezone-offset
Dev Aggarwal
Dev Aggarwal

Reputation: 783

MySQL - Fetch date without timezone offset

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.

Upvotes: 10

Views: 6531

Answers (4)

Kavinda Shehan Rajapakshe
Kavinda Shehan Rajapakshe

Reputation: 174

First, This not an error. mysql npm do this to avoid time conflicting over difference time zones. But when we are working locally this is bit disgusting.

To avoid this you can add a connection option called, 'timezone' and set it to 'local' or 'Z'

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : 'example.org',
    user     : 'bob',
    password : 'secret',
    timezone : 'local'
});

Reference :- mysql npm doc

Upvotes: 1

Dev Aggarwal
Dev Aggarwal

Reputation: 783

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'
});`

Upvotes: 9

Matt W
Matt W

Reputation: 130

You might try setting dateStrings to true in your mysqljs connection parameters. The issue here is that mysqljs is converting it to a javascript date object. This will make it return as a string with your expected data.

Upvotes: 3

Seva Kalashnikov
Seva Kalashnikov

Reputation: 4392

If you want just to ignore the timezone use MySQL date:

select date(dob) from table1

but result will be 'yyyy-mm-dd'

If you want it to be formatted like 2001/02/02:

select DATE_FORMAT(dob,'%y/%m/%d') from table1

Upvotes: 1

Related Questions