Convert datetime to UTC while saving

[bookshelf]

Convert datetime to UTC while saving

[cliedelt]

This is still important. Any updates?

[tgriesser]

Yeah, I'm working on a typecasting module & hooks that will help with this. In the meantime you can filter this stuff with a custom model.format method.

[amitava82]

Excellent! Yes I was planning of using format and parse for now. Looking forward to it and validation.

[sudhirvkumar]

Any update on this is appreciated.

[johanneslumpe]

@tgriesser Why are we even using datetime and not timestamp ? Wouldn't the whole issue just vaporize, if we used a timestamp type for those fields? (which then could just receive the value from either new Date().getTime() or Date.now())

Reading the date back would of course use the timezone of the server, but would yield the correct time, in contrast to saving a string into a datetime field, which has been converted to UTC earlier. Like this:

var d = new Date(1389166613497) // just passing in the timestamp for a consistent example
var dTS = d.getTime() // => 1389166613497
var dString = d.toJSON() // => "2014-01-08T07:36:53.497Z"

If we were able to save dString into the database everything would be fine, since recreating a date from it would yield the correct result, like this:

// the date will be correctly converted to the local timezone
var dateLater = new Date(dString); 

But since we only have a string representation like 2014-01-08 07:36:53, recreating the date from the database will result in this:

// will specify the date in the local timezone, even though it's UTC
var dateFromDB = new Date("2014-01-08 07:36:53");
dateFromDB.toJSON() // => "2014-01-08T05:36:53.000Z" - oops

Apart from hitting a bug in 2038 - is there anything that makes this not feasible?

[johanneslumpe]

gotta bump this one again @tgriesser ;) Any opinion about this? Or any progress which hasn't surfaced yet?

[SkylerRogers]

@tgriesser - how safe/reliable is it to handle this issue via the knex config (assuming you desire everything to be stored in UTC)?

For example something like this:

module.exports = {
  development: {
    client: 'mysql',
    connection: {
      host: 'localhost',
      user: 'root',
      password: '',
      database: 'my_database',
      timezone: 'UTC'
    }
  }
}

[crunchtime-ali]

The project leadership of Bookshelf recently changed. In an effort to advance the project we close all issues older than one year.

If you think this issue needs to be re-evaluated please post a comment on why this is still important and we will re-open it.

We also started an open discussion about the future of Bookshelf.js here #1600. Feel free to drop by and give us your opinion.
Let's make Bookshelf great again