CODEWITHSUNDEEP
postgresqldateknex.js
Trung Tran
Trung Tran

Reputation: 13721

postgresql returns incorrect date format

I am building an app using node.js with postgresql db and knex ORM. I have a column type date. I uploaded date data in the format YYYY-MM-DD (2016/05/12). When I view the data in pgAdmin, the data correctly shows as 2016/05/12. However, when I retrieve the data, it displays in the format:

Wed May 11 2016 20:00:00 GMT-0400 (EDT)

Does anyone know why this might be happening? Here's my query:

knex('projects').where({
    report_id: req.params.id
}).then(function(data) {
    console.log(data[0].created_at); //returns Wed May 11 2016 20:00:00 GMT-0400 (EDT)
    res.send(data);
}).catch(function(error) {
    console.log('error: ' + error);
    res.sendStatus(500);
});

The query returns:

Wed May 11 2016 20:00:00 GMT-0400 (EDT) - which is off by a day adds additional information that i did not originally input into the database.

Does anyone know what might be going on?

Thanks in advance!

Upvotes: 1

Views: 2040

Answers (1)

skilbjo
skilbjo

Reputation: 514

There is no date data type in javascript. Your date sql data type is being casted as a datetime javascript data type, and that's why timezone information is being appended.

To fix it, try having the call to the database return the field as varchar, (ie something like var data = db.query('select date::varchar from table',conn) then javascript will think it's string, and thus data integrity of the field is preserved.

Upvotes: 4

Related Questions