I have a problem retrieving the dates in my Rest API.
I insert a data and the datetime is automatically filled in my DB. for instance.
2017-09-27T23:16:46.000Z
this value that is auto generated and i can see it with mysql workbench.
But when I retrieve with my API which is a basic query like this:
select * from inv;
It returns me 2017-09-27T21:16:46.000Z
with two hours less than the value of the database.
What could be the problem?
I insert two images so you can see the table and the API response.
Greetings and thank you.
EDIT:
This is the code I use to launch the request to the DB:
function getInvTop(req, res) {
if (connection)
{
connection.query('SELECT * FROM invjuan WHERE attrName = "sensor2" ORDER BY recvTime DESC LIMIT 15', function(err, rows) {
if(err)
{
throw err;
console.log('Error al Conectar' + error);
}
else
{
res.status(200).json(rows);
}
});
}
}
For the answer for now I just call the method from Postman.
As a client I am using Angular4
, I don't know if the correct thing is to format the date from it.
If I run this query it gives me the following error:
SELECT recvTimeTs, CONVERT_TZ('recvTime','GMT','MET'), fiwareServicePath, entityId, entityType, attrName, attrType, attrValue, attrMd FROM iof.invjuan WHERE attrName = "sensor2" ORDER BY recvTime DESC LIMIT 15;
Hi Manolait,
It seems that your problem is related to the UTC dates, let me explain.
Apparently, your Mysql Workbench is in charge of converting your date automatically to the date of your TimeZone (pc time), but the real value that you have in the database is
2017-09-27T23:16:46.000Z
(UTC), so when you do yourselect * from inv
from your application, returns the time in UTC and you have to apply the change to the TimeZone.I guess from the difference in dates that you show us (2 hours) right now you are in
GMT +2
, that is, Europe, so a solution to your problem would be to insert the following in the select:Manual
Hello, Thanks to what Marc contributed, I solved the problem in the following way:
Thank you very much.