Problem retrieving datetime

0

I have a problem retrieving the dates in my Rest API.

I insert a data and the datetime is automatically filled in my BD. for example.

2017-09-27T23:16:46.000Z this value that is generated automatically and I can see it with mysql workbench.

But when I recover with my API it is a basic query like this:

select * from inv;

Returns 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 that you can see the table and the response of the API.

Greetings and thanks.

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 only call the method from Postman.

As a client I am using Angular4 , I do not know if the correct thing is to format the date from this one.

If I launch 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;
    
asked by Manolait 28.09.2017 в 00:40
source

2 answers

0

Good Manolait,

It seems that your problem is related to the UTC dates, I mean.

Apparently, your Mysql Workbench is responsible for converting your date automatically to the date of your TimeZone (pc time), but the actual value you have in the database is 2017-09-27T23:16:46.000Z (UTC), then to the time to do your select * from inv from your application, it returns the time in UTC and you have to apply your change to the TimeZone.

I feel for the difference of dates that you show us (2 hours) right now you are in GMT +2 , that is, Europe, then a solution to your problem would be to insert the following in the select:

CONVERT_TZ(nombre_campo_fecha,'GMT','MET')

Manual

    
answered by 28.09.2017 / 08:59
source
0

Good morning, Thanks to what Marc contributed, solve the problem in the following way:

SELECT recvTimeTs, CONVERT_TZ(recvTime, "+00:00", "+02:00") AS recvTime, fiwareServicePath, entityId, entityType, attrName, attrType, attrValue, attrMd FROM iof.invjuan WHERE attrName = "sensor1" ORDER BY recvTime DESC LIMIT 15

Thank you very much.

    
answered by 02.10.2017 в 11:27