How to prevent JSON.stringify() from altering date?

Question

I read all pages on SO about this, but none has the simple solution for what I need.

In MySQL database, I have DATETIME field called 'start'. An example record in it is:

2015-12-28 06:20:00

When I fetch this from database, date in result object is same as in database, but when I use JSON.stringify method on result object, it saves this date as 2015-12-28 05:20:00.

How can I prevent this, I just want the same date as it is in database, without any conversion to local timezone?

EDIT:

This is how my SQL query result looks like:

start: Mon Dec 28 2015 06:20:00 GMT+0100 (Central Europe Standard Time),

And this is after calling JSON.stringify:

"2015-12-28T05:20:00.000Z"

My computer is in GMT+0100. But I don't want any timezone information to be stored in JSON, neither in MySQL database. I get date from an external API in ISO 8601 format, but I don't want to store timezone information.

The JSON.stringify() method converts a JavaScript value to a JSON string. Do not alter your code. https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify — Sadikhasan, Dec 28 '15 at 05:20
looks like a timezone problem try using `UTC` time zone [date_default_timezone_set('UTC')](http://php.net/manual/en/function.date-default-timezone-set.php) — bansi, Dec 28 '15 at 05:22
not familiar with nodejs this answer may help you [How to use timezone offset in Nodejs](http://stackoverflow.com/questions/10615828/how-to-use-timezone-offset-in-nodejs) — bansi, Dec 28 '15 at 05:26
So serialise it manually the way you like; it behaves like it behaves because the standard requires it to do so. — zerkms, Dec 28 '15 at 06:14
But it would probably slow down my server..I have many other columns beside 'start'. Right now I just call JSON.stringify(result), where 'result' is whole result of SQL select query. There must be some better solution, this is not an rare situation. What impact would make changing type of field from DATETIME to VARCHAR? I could sort it that way too. I just don't know if it is slower than DATETIME? — kecman, Dec 28 '15 at 06:19
"But it would probably slow down my server" --- performance optimisation is always about measuring. First measure - then make any statements. The same is applied to your other question about `varchar` vs `datetime` (which is nonsense) — zerkms, Dec 28 '15 at 06:23
The thing is - your library for some reason parses the date as the js `Date` object. And what you need is - format it either in a query or in JS manually in the way you want it to look like. — zerkms, Dec 28 '15 at 06:27
Main problem is I'm working with an external API. They provide schedule for gym classes, and store start time in ISO 8601 format, BUT not caring about timezone. This company is based in USA, so they store time as i.e. 06:20 UTC , even that gym class starts at 06:20 in local american time.. I must make my code independent from server time, as I could migrate it from time to time. So I don't need to know anything about server or end client's timezone. Just save start time from API to database, and then select it and show on my page unmodified. — kecman, Dec 28 '15 at 06:33

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

var db_config = {
     host     : 'localhost',
     user     : 'xxx',
     password : '',
     database : 'xxx',
     timezone: 'utc'  //<-here this line was missing
};

That timezone variable solved my problem. I found the answer here:NodeJS responded MySQL timezone is different when I fetch directly from MySQL

edited May 23 '17 at 11:52

Community

1
1

answered Dec 28 '15 at 06:47

kecman

813
3
14
34

How to prevent JSON.stringify() from altering date?

1 Answers1