so I am using MySQL as the database for my node app I can insert the date in YYYY-MM-DD format but when I get data from it returns it as yyyy-mm-ddT00:00:00.000Z so I want only the first part

db.query(`SELECT agereement_date FROM mt_A1 WHERE ledger_num = 15`,(err,data)=>{
  console.log(data)

})

the output is like this

[
  RowDataPacket {
    agereement_date: 2021-03-07T18:30:00.000Z,
  }
]

I Want only the YYYY-MM-DD the first part I am using some JavaScript to rectify it but it feels unnecessary is there a way to get the date in that format directly from MySQL

CodePudding user response：

Time in MySQL db is in without definition of timezone.

NodeJS converts time to Date object when You get it from server.

So there are 3 solutions depending on Your use case:

1. if You don't care timezone of browser: You can add DATE_FORMAT to sql query and take date part only.

2. it's still not timezone aware, but You can take date from db on backend and cut date part of it using:

db.query('SELECT agereement_date FROM mt_A1 WHERE ledger_num = 15', (err,data) => {

  data = data.map(row => {
    row.agereement_date = row.agereement_date.toISOString().split('T')[0];
    
    return row;
  });
  
  console.log(data);
})

3. If You want to show correct time for timezone of browser then You must work with that date object on frontend level:

function strftime(sFormat, date) {
  if (!(date instanceof Date)) date = new Date();
  var nDay = date.getDay(),
    nDate = date.getDate(),
    nMonth = date.getMonth(),
    nYear = date.getFullYear(),
    nHour = date.getHours(),
    aDays = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'],
    aMonths = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
    aDayCount = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334],
    isLeapYear = function() {
      if ((nYear&3)!==0) return false;
      return nYear0!==0 || nYear@0===0;
    },
    getThursday = function() {
      var target = new Date(date);
      target.setDate(nDate - ((nDay 6)%7)   3);
      return target;
    },
    zeroPad = function(nNum, nPad) {
      return (''   (Math.pow(10, nPad)   nNum)).slice(1);
    };
  return sFormat.replace(/%[a-z]/gi, function(sMatch) {
    return {
      '%a': aDays[nDay].slice(0,3),
      '%A': aDays[nDay],
      '%b': aMonths[nMonth].slice(0,3),
      '%B': aMonths[nMonth],
      '%c': date.toUTCString(),
      '%C': Math.floor(nYear/100),
      '%d': zeroPad(nDate, 2),
      '%e': nDate,
      '%F': date.toISOString().slice(0,10),
      '%G': getThursday().getFullYear(),
      '%g': (''   getThursday().getFullYear()).slice(2),
      '%H': zeroPad(nHour, 2),
      '%I': zeroPad((nHour 11)   1, 2),
      '%j': zeroPad(aDayCount[nMonth]   nDate   ((nMonth>1 && isLeapYear()) ? 1 : 0), 3),
      '%k': ''   nHour,
      '%l': (nHour 11)   1,
      '%m': zeroPad(nMonth   1, 2),
      '%M': zeroPad(date.getMinutes(), 2),
      '%p': (nHour<12) ? 'AM' : 'PM',
      '%P': (nHour<12) ? 'am' : 'pm',
      '%s': Math.round(date.getTime()/1000),
      '%S': zeroPad(date.getSeconds(), 2),
      '%u': nDay || 7,
      '%V': (function() {
              var target = getThursday(),
                n1stThu = target.valueOf();
              target.setMonth(0, 1);
              var nJan1 = target.getDay();
              if (nJan1!==4) target.setMonth(0, 1   ((4-nJan1) 7)%7);
              return zeroPad(1   Math.ceil((n1stThu-target)/604800000), 2);
            })(),
      '%w': ''   nDay,
      '%x': date.toLocaleDateString(),
      '%X': date.toLocaleTimeString(),
      '%y': (''   nYear).slice(2),
      '%Y': nYear,
      '%z': date.toTimeString().replace(/. GMT([ -]\d ). /, '$1'),
      '%Z': date.toTimeString().replace(/. \((. ?)\)$/, '$1')
    }[sMatch] || sMatch;
  });
}



const time = new Date('2021-03-08T00:30:00.000 02:00');
const localTime = strftime('%Y-%m-%d %H:%M:%S', time);
const localDate = strftime('%Y-%m-%d', time);

console.log(localTime)

console.log(localDate)

as You can see I've defined in ISO format with 02:00 so it may return differently depending on time of local computer.

CodePudding user response：

Modify your query :

SELECT DATE(agereement_date) 
FROM mt_A1 
WHERE ledger_num = 15