transform mysql timestamp field to a formatted date dd/mm/yyyyy

Question

I have a simple blog table with a field "date" of type current timestamp so in the database it shows as 2019-10-09 01:37:12 when I display the date with a simple query

connection.query('SELECT * FROM `soccer`', function (error, results, fields) {

        console.log(results)


        res.render("index.ejs", {
            results: results
        });
    });

});

I get the date displayed as Sun Oct 06 2019 00:00:00 GMT+0200 (GMT+02:00)

what I would like is the date in format dd/mm/yyyy so I wonder the best way to do this formatting either directly in the mysql query (if the DB can do that way not) or in javascript once I get the results. Thanks

In MySQL you can use DATE_FORMAT` = https://stackoverflow.com/search?q=date_format — FanoFN, Oct 10 '19 at 09:03

score 0 · Answer 1 · answered Oct 10 '19 at 08:51

0

in javaScript you can use this code:

function convert(str) {
  var date = new Date(str),
    mnth = ("0" + (date.getMonth() + 1)).slice(-2),
    day = ("0" + date.getDate()).slice(-2);
  return [date.getFullYear(), mnth, day].join("-");
}

console.log(convert("Sun Oct 06 2019 00:00:00 GMT+0200 (GMT+02:00)"))

answered Oct 10 '19 at 08:51

mohammad javad ahmadi

2,031
1
10
27

for mysql is this link https://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query. but i dont know in `mysql` module in node worked or no – mohammad javad ahmadi Oct 10 '19 at 08:57

transform mysql timestamp field to a formatted date dd/mm/yyyyy

1 Answers1