mysql: store UTC, but when fetch its wrapped into JS local Date (allow user override how values returned by driver are converted to javascript values)

[kirrg001]

Hey,

i'm using the knex option timezone: UTC and i store UTC dates in the database in the format YYYY-MM-DD HH:mm:ss (this is the only format i can use, because i use type datetime). Everything is fine with storing the values.

My server is running in +02:00.
When i fetch the datetime fields from the database, the UTC dates are wrapped into JS native Date object, which is in local TZ.

Example:
stored value: 2016-05-05 10:00:00 (which is UTC)
fetched value: Tue May 05 2016 10:00:00 GMT+0200 (CEST) (type Date)

But this is wrong representation.
For postgres and sqlite i don't have these problems.
Postgres stores the values with offset (+00 or +02) and sqlite returns the dates as UTC strings if i want, which is fine.

This issue is maybe similar to this: #524

I saw that some people using knex timestamp type, but i assume it will not help fixing the issue.

Is there an option to tell Knex to not transform the stored values into a native JS Date?
Or is there a hack?
Thank you!

Knex Version: 0.10.0
Bookshelf Version: 0.9.5
Node Version: v4.4.2

[elhigu]

Looks like with mariadb driver interpreting datetimes to javascript dates are done in knex side. I suppose knex should provide way to give user defined adapter how to interpret values returned by driver.

With postgresql dialect one can do this already, because pg driver allows user to change how data returned by driver is converted to javascript values.

[ErisDS]

@kirrg001 I just spotted this - how are you currently working around the issue? Or are we stuck because of this?

[kirrg001]

@ErisDS no i found a workaround for this issue.

let's take my example from my initial issue:
stored value: 2016-05-05 10:00:00 (which is stored as UTC)
fetched value: Tue May 05 2016 10:00:00 GMT+0200 (CEST)

var newValue = moment(moment(fetchedValue).tz(moment.tz.guess()).format('YYYY-MM-DD HH:mm:ss')).toDate();

• moment.tz.guess() will always return the original TZ even if we use moment.tz.setDefault()
• so the result of this code statement is Thu May 05 2016 12:00:00 GMT+0200 (CEST)
• and moment(newValue).format() is 2016-05-05T10:00:00Z

[elhigu]

@kirrg001 Is it always UTC in which timezone mysql returns datetime / timestamp columns or is there way to configure it? I was trying to set it by setting SET GLOBAL time_zone = '+10:00'; but I still got all values returned in UTC.

If we can trust that datetime/timestamp is always returned as UTC without timezone postfix, this bug should be easy to fix.

[kirrg001]

My mistake instead of

knex({ connection: { timezone: 'UTC'} })

i passed the timezone option flat:

knex({ timezone: 'UTC', connection: { ... } })

So now the fetched value is correct: Tue May 05 2016 12:00:00 GMT+0200 (CEST)
But still you can leave it as a feature request. When i configure knex to TZ UTC, it should maybe not return a local JS Date.

[mdziekon]

Has been there any progress on this bug/feature? @kirrg001's workaround does not work in every case there is (eg. when MariaDB server returns all dates in UTC string instead of "connection's TZ string"). Besides, it still a workaround, not a real solution to the problem.

[jbouloux]

Still got the same issue (UTC date on Database, wrapped in Date Object with 2 hours less:
2017-05-29T13:11:43.383Z to 2017-05-29T11:11:43.383Z) :/ Even if I add timezone: 'utc' in my connection config, nothing is changed. Is there a solution ?

[elhigu]

@jbouloux how are you printing your javascript date objects?

for example this shows date in local timezone:

> new Date('2010-01-01T00:00:00.000Z').toString()
'Fri Jan 01 2010 02:00:00 GMT+0200 (EET)'

but .toISOString() returns UTC:

> new Date('2010-01-01T00:00:00.000Z').toISOString()
'2010-01-01T00:00:00.000Z'

Also how is your code that writes times to mysql db?

[jbouloux]

@elhigu The db is in postgreSQL. I can't have access to the code that write in db, it has been written by someone else. The only thing I know is that the type of data is "timestamp without time zone", so UTC Timestamp.
If I ask for 2017-05-29T13:11:43.383Z in my query
I get 2017-05-29T11:11:43.383Z printed with .toISOString()
and 2017-05-29T13:11:43.383Z with .toString()

[elhigu]

@jbouloux because your timestamp is not timestamptz type postgresql returns it without timezone information

mikaelle=# select CAST('2017-05-29T13:11:43.383' as timestamp with time zone);
        timestamptz         
----------------------------
 2017-05-29 13:11:43.383+03
(1 row)

mikaelle=# select CAST('2017-05-29T13:11:43.383Z' as TIMESTAMP);
        timestamp        
-------------------------
 2017-05-29 13:11:43.383
(1 row)

Even that timestamp is in UTC format, node-postgres driver created javascript date from it with new Date('2017-05-29 13:11:43.383') which creates date object with 2017-05-29 13:11:43.383 of local time zone.

You might like to override how node-postgres converts timestamp column values to javascript objects https://github.com/brianc/node-pg-types.

[jbouloux]

@elhigu That's the perfect solution ! Thanks

[elhigu]

@jbouloux btw.

The only thing I know is that the type of data is "timestamp without time zone", so UTC Timestamp

Postgresql seems to say that timestamp without timezone should be considered as local time https://www.postgresql.org/docs/9.2/static/datatype-datetime.html, anyways this is wrong issue to talk about this (I hope that someone who knows how this stuff works with mysql will still comment this issue).

[elhigu]

just for the reference setting custom type parsing with mysql is done like:

var moment = require('moment');
var connection = require('knex')({
        client: 'mysql',
        connection: {
            host: db.host,
            user: db.user,
            password: db.password,
            database: db.database,
            timezone: 'UTC',
            typeCast: function (field, next) {
              if (field.type == 'DATETIME') {
                return moment(field.string()).format('YYYY-MM-DD HH:mm:ss');
              }
              return next();
            }
        }
   });

https://github.com/mysqljs/mysql#type-casting

[roytz]

In my case, the connection was a string so I had to find the date OID and use
pg.types.setTypeParser(DATE_OID, d => moment(d));

[elhigu]

I think this is not an issue anymore, since mariadb was deprecated and with mysql and postgres driver allows way to change how different column types are interpreted to javascript. Knex could maintain some presets for different drivers to make interpreting columns more compatible, but I don't think that it should be forced by default. If something like that is needed separate feature request may be added.

[m-majetic]

I experienced the same issue right now with Aurora (MySQL-5.7) from AWS.

Is there a list of possible configuration options in the knex documentation on knexjs.org ?
If it weren't for this Github issue, I wouldn't even have known that there is a "timezone" option for the database connection configuration.

[elhigu]

@m-majetic each driver has their own options. One should check those from each driver’s documentation.