2

I have a todo list that is stored in mysql database the columns stored are todoTitle and todoDate, when i print the todoDate to screen as you can see in the code below it will show the date decremented by one day, for example if the date in the database shows 2016-12-20 it will show 2016-12-19 on my website.

If you are wondering why the todoDate is made into string and then substring it is because if i would not do that it would print out like this: 2016-12-19T23:00:00.000Z

var xhr = new XMLHttpRequest();                 // Create XMLHttpRequest object

xhr.onload = function() {// When readystate changes
// The following conditional check will not work locally - only on a server
if(xhr.status === 200) {                      // If server status was ok
responseObject = JSON.parse(xhr.responseText);

// BUILD UP STRING WITH NEW CONTENT (could also use DOM manipulation)
var newContent =
    "<tr>" +
    "<td>" + "Activity" + "</td>" +
    "<td>" + "Due Date" + "</td>" +
    "</tr>";
for (var i = 0; i < responseObject.length; i++) { // Loop through object
    newContent += "<tr>";
    newContent += "<td>" + responseObject[i].todoTitle + "</td>";
    newContent += "<td>" + responseObject[i].todoDate.toString().substring(0,10) + "</td>";
    newContent += "</tr>";
}

// Update the page with the new content
document.getElementById('content').innerHTML = newContent;

}
};

//xhr.open('GET', 'data/data.json', true);  // Dummy JSON Data
xhr.open('GET', 'http://127.0.0.1:8000/todo/', true);        // Prepare the request
xhr.send(null);                                 // Send the request
random1234
  • 777
  • 3
  • 17
  • 41
  • timezone applying? you see that 23:00 and `Z` in full timestamp? – Vao Tsun Nov 23 '16 at 22:18
  • Regarding the `.toStrong().substring()`, are you unable to format the date server side? That would solve the ISO date string format. Also, could you provide the value and type of a `responseObject[i].todoDate` for me? – amflare Nov 23 '16 at 22:19
  • @VaoTsun it used to be of datatype TIMESTAMP in my mysql database but i changed it the column to a DATE datatype, and it does get saved in the YYYY-MM-DD format in my database which is what i want, but when i display it on the website it shows the full timestamp – random1234 Nov 23 '16 at 22:33
  • @tbirrell not sure what you are asking for regarding both your questions since i'm a beginner...=( – random1234 Nov 23 '16 at 22:34
  • it seems you use datetime column type (but thus "Z" is confusing, do you have some additional processing on server?) in any case 88.8% it's timezone issue, read this: http://stackoverflow.com/questions/19843203/how-to-store-a-datetime-in-mysql-with-timezone-info – 2oppin Nov 23 '16 at 22:36
  • @random1234 insert these two lines of code inside the for-loop, then copy and paste the output into your question as an edit. (You get to the output by hitting F12 in your web browser.) `console.log(responseObject[i].todoDate); console.log(typeof responseObject[i].todoDate);` – amflare Nov 23 '16 at 22:45
  • @tbirrell 2016-11-24T23:00:00.000Z string 2016-11-24T23:00:00.000Z string etc..... – random1234 Nov 23 '16 at 22:48
  • @random1234 Ok. So is that "2016-11-24T23:00:00.000Z" supposed to be "2016-11-25..." Or is it correct at this point in the program? (Also, as a side note, `.toString()` is unnecessary since it is already a string) – amflare Nov 23 '16 at 22:51
  • @tbirrell yes it is wrong, it is supossed to be 2016-11-25 – random1234 Nov 23 '16 at 22:54
  • Okay, your error is server side then, and we will need to look at that code to determine the issue. – amflare Nov 23 '16 at 22:56

1 Answers1

2

The Z means "zero hour offset" also known as "Zulu time" (UTC). When you are querying the date from the database there are two possible scenario that the date is change, either in the database layer or on the application layer, adjusting it to the timezone you are at.

So for example if the database setting save time automatically to UTC when you got the actual data it will be converted to your current time zone. But from your example 2016-12-20 is converted to 2016-12-19T23:00:00.000Z then I am assuming that your database setting on date is saving it in a certain timezone then converting it to UTC.

To fix it try adjusting your application logic or database setting, for me I rather do it on the application level and maintain the date in DB to be save to UTC.

Try this to see the difference and could give you hint on solving your issue:

var currentDate = new Date();
var isoDate = currentDate.toISOString();
console.log(currentDate, isoDate);
Wangot
  • 91
  • 1