Date tranformations

[randallmeeker]

Notice the 3 objects below. Dates are getting modified when I try to push them to sails. The original date objects send to the server are in ISO string as you can see below. Somewhere before the update my dates are being re-calculated for timezone offset?

I'm on sails.js 0.10.5, waterline 0.10.10, sails-mysql 0.10.5

Original Data sent to server
{"shiftStart": "2014-06-20T01:00:00.000Z",
"shiftEnd": "2014-06-20T13:00:00.000Z"}

These is the result of sending 'values' to the console.log on my model callbacks.
beforeValidate
{ shiftStart: Fri Jun 20 2014 20:00:00 GMT-0500 (Central Daylight Time),
shiftEnd: Sat Jun 21 2014 08:00:00 GMT-0500 (Central Daylight Time)}

beforeUpdate
{ shiftStart: Fri Jun 20 2014 20:00:00 GMT-0500 (Central Daylight Time),
shiftEnd: Sat Jun 21 2014 08:00:00 GMT-0500 (Central Daylight Time)}

afterUpdate
{ shiftStart: Fri Jun 20 2014 15:00:00 GMT-0500 (Central Daylight Time),
shiftEnd: Sat Jun 21 2014 03:00:00 GMT-0500 (Central Daylight Time)}

[particlebanana]

MySql is a tricky one. It doesn't allow us to store timezones in the database so you only get to store something that looks like: 2014-06-19 20:00:00. It relies on the mysql database knowing what timezone the server lives in or the application to also store the timezone and have code new date it after.

Waterline will take the iso strings you send and new Date them because it works with javascript dates and not strings. It normalizes it into a format that is independent of what the client sends, so here your server is in central time. When new Date(Date.parse(value)); is run here: https://github.com/balderdashy/waterline/blob/master/lib/waterline/core/typecast.js#L203-L214 you get the times you see logged. Then MySQL will take that and either store it in it's known local time or in the timezone specified in the config.

You can specify a timezone for the mysql connection to use in your connections config:

myMysqlConnection: {
    database: 'foo',
    timezone: 'Z',
    ...
}

If you need exact times for calculations I would suggest storing integer epoch times which you can use independent of any mysql timezone trickery. Other databases like Postgres allow the timezone to be stored in the date so it's not an issue when you new Date() it on the client.

[randallmeeker]

Sorry, I should have mentioned that I am already using timezone:'UTC'. So there still should be no transformations. Also the newly tranlsated date by new Date() "Fri Jun 20 2014 20:00:00 GMT-0500 (Central Daylight Time)" should translate back to "2014-06-20T01:00:00.000Z" because it has the offset and although MySQL does not store timezone info, it will translate an timestring with an offset just fine. In this instance its as if sails is sending Fri Jun 20 2014 20:00:00 and chopping off GMT-0500 (Central Daylight Time).

I have already made the switch to using INT for my dates. I have found that between sails,node,"the client" and my sql everyone wants to mess with your dates and I'm just tired of it. But there is something wrong somewhere in there and I'm not sure this issue should be closed.

[eddieajau]

@particlebanana I think this is a problem with the sails-mysql adapter itself. I had originally thought we could work around it by manipulating the date with onBeforeCreate and onBeforeUpdate events, but I've realised that it will yield different behaviour when swapping between sails-memory for testing and sails-mysql in production.

That said, would you entertain the ability to pass in an override for toSqlDate in the adapter configuration? I think that's the only way to settle it once and for all given that each server could be different. That's going to be a little tricky to work out, but I think it could be done.