Home / Questions / 414518
Accepted
metamax
Asked: 2021-12-15 12:34:37 +0800 CST 2021-12-15 12:34:37 +0800 CST

Error in a database query in NodeJs

  • 772

I don't know if the title is appropriate because more than an error I have a strange behavior in my NodeJs application with express and Postgresql

To begin with, I insert my DB into a field called date of the timestamp type in the following way:

var newDate = new Date();
// agrego 5 minutos a la fecha y hora actuales
newDate.setMinutes(newDate.getMinutes() + 5);
// convierto a string el resultado
var string_newDate = newDate.toISOString().replace(/T/, ' ').replace(/\..+/, '')
console.log('tipo:', typeof(string_newDate), ', valor:',string_newDate);
//: tipo: string  valor: 2020-12-14 20:17:53
// guardo en la DB:
database.query('INSERT INTO usuario(fecha) VALUES ($1)', [string_newDate])

When doing a query to the DB by terminal it shows me exactly that value:

Select fecha from usuario where id = 1;
// obtengo: 2020-12-14 20:17:53

The problem arises when I make the query from my nodejs application:

const result = await database.query('SELECT fecha from usuario where id = 1');
...
console.log('tipo:', typeof(result.rows.fecha), 'valor:', typeof(result.rows.fecha))
// obtengo esta fecha:
// tipo: object valor: 2020-12-15T01:17:53.000Z

The date I get when doing the query with nodejs is 2020-12-15T01:17:53.000Z not what I have stored in my database which is 2020-12-14 20:17:53 .

As additional data, no matter what date I save, the date I get when doing the query with nodejs is 5 hours above the one I saved , as in this case.

I appreciate any help with this problem, I'll be here as long as it takes.

nodejs

1 Answers

  1. Best Answer

    When you do the query your database returns the date using UTC, curiously it is similar to the problem described in this question in the morning.

    See what you get (starting from the last piece of code in your question) is:

    2020-12-15T01:17:53.000Z

    This is an ISO string and the Zat the end indicates that it is UTC time , therefore it is equivalent to 2020-12-14 20:17:53(from your point of view/local time).

    You can fix this by changing it to the local date format:

    const result = await database.query('SELECT fecha from usuario where id = 1');
...
const fecha = new Date(result.rows.fecha);
console.log('tipo:', typeof(Date.toLocaleString()), 'valor:', typeof(Date.toLocaleString()))

    In summary... You are not seeing different dates, what happens is that you are seeing the same date in different formats (UTC and UTC-5).

    • 1