I'm trying to get Node to query my database and send the results to the client where it is placed into a <table>, but with the timestamp removed from the date.

I have the following bit of Node querying my database:

pool.query(
    "SELECT DATE(completed), duration, submitted FROM workentries WHERE iin = ?",[iin], 
    function(error, results) {
        if(error) throw error;
        connection.release()
        res.send(results);
    }
)

The client-side JS:

const httpr = new XMLHttpRequest();
httpr.onload = () => {
    let responsej = JSON.parse(httpr.response)
    for(i in responsej) {
        document.getElementById("workentry-table").innerHTML  = "<tr><td>" responsej[i].completed "</td><td>" responsej[i].duration "</td><td>" responsej[i].submitted "</td></tr>";
    }
}
httpr.open('GET', '/workentry-list');
httpr.send();

This is the result:

This is how the dates are formatted in the database:

I'm also going to want to reformat the timestamp on the submitted datetime, so it'd be a big help if I can find out how to properly format date/time for this case.

(I'm sorry if this is obvious, but I'm relatively new to Node. I Googled like a madman but couldn't find a solution, which either means this is an uncommon issue or I'm doing something stupid)

EDIT:

• Fixed undefined by using DATE(completed) AS completed to the query
• Fixed the date/time format by using toLocaleString() in the client-side

CodePudding user response：

You need to give an alias to the column in the SELECT list so you can refer to it as responsej[i].completed. Otherwise, you need to use responsej[i]['DATE(completed)']

And to format it as you want, use the DATE_FORMAT() function rather than returning a DATE.

SELECT DATE_FORMAT(completed, '%Y-%m-%d') AS completed, duration, submitted FROM workentries WHERE iin = ?