Date in response JSON is different than in database

Question

I have sails.js application. When I add record to my MySQL database there is field createdAt - type dateTime. In MySql console createdAt value looks like Wed Mar 25 2015 00:00:00 GMT+0100 When I try to get this data as JSON using sails.js controller I get 2015-03-24T23:00:00.000Z - which is 1 hour elier? My system time zone is GMT +1. How can I get the same time in db and JSON rsponse?

   'list': function (req, res, next) {

 Tests.find().exec(function (err, tests) {
   if (err) res.json({ error: 'DB error' }, 500);
   if (tests) {
     _.each(tests, function (data) {
       console.log("Date: " + JSON.stringify(data.createdAt)+"   "+data.createdAt);
     })
     res.status(200).json(tests);
   }
 });

And console output is:

 Date: "2015-03-10T14:28:51.000Z"   Tue Mar 10 2015 15:28:51 GMT+0100
Date: "2015-03-16T10:25:34.000Z"   Mon Mar 16 2015 11:25:34 GMT+0100
Date: "2015-03-16T11:27:23.000Z"   Mon Mar 16 2015 12:27:23 GMT+0100
Date: "2015-03-16T11:33:39.000Z"   Mon Mar 16 2015 12:33:39 GMT+0100
Date: "2015-03-16T11:34:41.000Z"   Mon Mar 16 2015 12:34:41 GMT+0100
Date: "2015-03-16T12:13:21.000Z"   Mon Mar 16 2015 13:13:21 GMT+0100
Date: "2015-03-16T13:54:03.000Z"   Mon Mar 16 2015 14:54:03 GMT+0100

It all happends while converting to JSON - still dont know how to fix it.

Sounds like something's applying the time zone offset twice. You might want to check that by temporarily going into a different time zone, e.g. for the US. Next, post the code - we don't know whether the problem is when you fetch the data from the database, or when it's serialized as part of the response. — Jon Skeet, Mar 16 '15 at 13:37
the data is looks ok, i think the problem is in the print that are in different format — Ran Adler, Mar 16 '15 at 13:50
Possible duplicate of [stackoverflow question 1486476][1] [1]: http://stackoverflow.com/questions/1486476/json-stringify-changes-time-of-date-because-of-utc — jolian, Mar 16 '15 at 14:20
It is similar, but solution dosent fit here. My server and client is in the same time zone and when I send resultset from query in response as json I dont want to iterate over resultset to convert all date fileds. — pablo, Mar 16 '15 at 14:33

Pavel Gatnar · Answer 1 · 2015-03-16T19:45:52.260

1

JSON.stringify(data.createdAt) is equal to data.createdAt.toJSON() and it is equal to data.createdAt.toISOString()

data.createdAt is equal to data.createdAt.toString()

the time value is equal; toString() returns it according your time zone, while toISOString() in GMT.

to get the Date from JSON string use conversion function as 2nd parameter of JSON.parse(), you can use the regexp rx = /([0-9]{4})-([0-9]{2})-([0-9]{2})T([0-9]{2}):([0-9]{2}):([0-9]{2})\.([0-9]{3})Z/ to check the date ISO format and then convert it to date d = new Date(str)

edited Mar 16 '15 at 19:45

answered Mar 16 '15 at 15:31

Pavel Gatnar

3,987
2
19
29

new Date('2015-03-16T21:10:30.000Z') or new Date(1426540230000) create UTC date 2015-03-16 21:10:30; new Date(2015,2,16,21,10,30,0) creates your local date 2015-03-16 21:10:30 GMT+-your time zone; new Date() creates your current local date – Pavel Gatnar Mar 16 '15 at 20:18

score 0 · Answer 2 · answered Mar 17 '15 at 14:21

0

What is the type of createdAt in your Mysql database? Is it Date? If yes try setting it to varchar. And if you need to manipulate it or format it you can use moment js.

answered Mar 17 '15 at 14:21

Emmanuel Campos

652
1
9
24

Date in response JSON is different than in database

2 Answers2