1

I have to retrieve some data from database of previous 30 days from today.My query gives correct output in sqlyog as I am using Mysql. But in nodejs It gives me one day previous output.Like today is 4 august.It gives output in nodejs from 3 august to previous 30 days. Thank you .

Node.js Script

app.get('/chart_5_data', function (req, res) {
    connection.query("SELECT COUNT(sms) AS sms, COUNT(DISTINCT(msisdn)) AS USER, DATE(DATETIME) AS DATE FROM mt_log WHERE (STATUS = 'Charge Success') AND (DATE(DATETIME) BETWEEN (CURDATE() - INTERVAL 30 DAY ) AND CURDATE()) GROUP BY (SELECT EXTRACT(DAY FROM DATETIME)) ORDER BY DATETIME ASC", function (error, results, fields) {
        if (error) throwerror;
        var data=JSON.parse(JSON.stringify(results));
        console.log(data);
        //console.log(data);
        res.send(data);
     }); 

 });

Sql Query In sqlyog

SELECT COUNT(sms) AS sms, COUNT(DISTINCT(msisdn)) AS USER, DATE(DATETIME) AS DATE FROM mt_log WHERE (STATUS = 'Charge Success') AND (DATE(DATETIME) BETWEEN (CURDATE() - INTERVAL 30 DAY ) AND CURDATE()) GROUP BY (SELECT EXTRACT(DAY FROM DATETIME)) ORDER BY DATETIME DESC

EDIT:

Both Node.js and SQLyog return SYSTEM if I execute SELECT @@session.time_zone;

CURRENT_DATE() gives output in sqlyog 4 august, but in nodejs it gives 3 august.

Wodin
  • 3,243
  • 1
  • 26
  • 55
Dipto Roy
  • 163
  • 1
  • 14
  • Why `GROUP BY (SELECT EXTRACT(DAY FROM DATETIME))`? Wouldn't `GROUP BY DAY(DATETIME)` do?, but given that you have `DATE(DATETIME) AS DATE` in the `SELECT` clause, it seem you actually want `GROUP BY DATE(DATETIME)`. Also, you have `ASC` in one query and `DESC` in the other. Did you by any chance run one query before midnight and the other after midnight? – Wodin Aug 04 '19 at 06:06
  • I have updated the query as said. I am running both the query right now.But still the same problem. Thank you for your response @ Wodin – Dipto Roy Aug 04 '19 at 06:20
  • Also, is the same `time_zone` being used for both sessions? See https://stackoverflow.com/a/19069310/495319 – Wodin Aug 04 '19 at 06:22
  • Not same time zone.My country timezone is GMT+6 .Not sure about the server timezone. – Dipto Roy Aug 04 '19 at 06:47
  • What does `SELECT @@session.time_zone;` give you in each case? – Wodin Aug 04 '19 at 06:48
  • By "each case", I mean when executed by Node.js vs. SQLyog. – Wodin Aug 04 '19 at 06:56
  • both gave output : "SYSTEM" – Dipto Roy Aug 04 '19 at 06:58
  • Try it with a specific date instead of `CURRENT_DATE()` for both queries, just as a sanity check. Also try executing `SELECT CURRENT_DATE()` from Node.js and SQLyog to compare them. I suspect this has something to do with time zones, but I don't have a lot of experience with time zones in MySQL. Sorry, I think I've reached the end of what I can help with. – Wodin Aug 04 '19 at 07:01
  • CURRENT_DATE() give output in sqlyog 4 august. but in nodejs it gives 3 august. – Dipto Roy Aug 04 '19 at 07:09
  • Try this: https://stackoverflow.com/a/23571551/495319 or one of the other answers from that question. – Wodin Aug 04 '19 at 07:14
  • I ran my code on real server not localhost.Then it solved my problem. Thank you very much for your response. – Dipto Roy Aug 04 '19 at 09:57
  • Ah. I thought both node and sqlyog were connecting to the same server. Next time that would be something worth mentioning in your question. – Wodin Aug 04 '19 at 09:59
  • They were connecting in same database server. But the domain was different in nodejs. – Dipto Roy Aug 04 '19 at 10:50

0 Answers0