Date-fields are returned as ISO8061 date-time strings

[bockelie93]

Situation

I have a date field, stored on the format YYYY-MM-DD (i.e. 2019-02-27).

Expectations

I expect it to be returned on the same format as the YYYY-MM-DD. (2019-02-27).

Results

I end up retrieving the value as an ISO8061 date-time string. YYYY-MM-DDThh:mm:ss... Z. (2019-02-26T23:00:00.000Z).

Workaround:

Simply return the value from the database, rather than converting to a Date-object.

import { types } from 'pg';

const DATE_OID = 1082;
const parseDate = (value) => value;

types.setTypeParser(DATE_OID, parseDate);

pg version 7.7.1

knex/knex#3071

[charmander]

I end up retrieving the value as an ISO8061 date-time string. YYYY-MM-DDThh:mm:ss... Z. (2019-02-26T23:00:00.000Z).

It’s a Date, not a string.

[bockelie93]

Even if it's a Date object, it does not know if it is date or date-time, which the date-field in the db is supposed to preserve.

The expected return value would be a date without time. Since the Date-object includes time, you shouldn't turn it into a date object.

[nareshbhatia]

I just ran into the same issue. The whole point of postgres date type is that it is a pure date, without any notion of time or timezone. So converting this to a JavaScript Date type is incorrect. It should be left as a string.

If the notion of time was needed then the timestamp data type should be used.

[boromisp]

For what it worth, the parsed Date object returns the expected values for .getFullYear(), .getMonth() and .getDate(). Unfortunately, that doesn't matter, if the value is serialized to string before using it since the .toISOString() doesn't preserve the original time zone offset.
The same is true for the timestamp type. Date objects can only fully represent timestamp with time zone values.

I think, by default, the library should return both the date and timestamp values as strings since the current behavior breaks if the server time zone is anything other than UTC.

[nareshbhatia]

Agree. @boromisp. Handling dates and times is an immensely complex subject. For inspiration and hope, I always watch this talk by Matt Johnson, a key contributor to moment.js.

[dcousens]

Time wasted around this.
Please return a YYYY-MM-DD string, not a javascript Date.

[ferrao]

The problem is not if the return value is a Date or a string.

If 2019-10-21 is present on the DB, when the return value from the query is converted into a string I get : 2019-10-20T23:00:00.000Z, which is not what I want.

But if I ran console.log(new Date('2019-10-21')) in node I get 2019-10-21T00:00:00.000Z, which is OK to me, which tells me that returning a Date and not a string is not the issue at all!

In fact, I fixed all my issues with:

const PG = require('pg');
const DATE_OID = 1082;
const parseDate = value => new Date(value); // returning Date, so this is not the problem!!!
PG.types.setTypeParser(DATE_OID, parseDate);

So I now raise the question, what is node-postgres doing with these date fields?

[ferrao]

I don't see dateToStringUTC doing anything wrong @dcousens, but that dateToString is totally guilty of the behaviour I am experiencing.

First line of code var offset = -date.getTimezoneOffset() returns an offset of 60, which I confirmed running in node:

> new Date('2019-10-21').getTimezoneOffset();
-60

But such offset makes no sense at all. It's just a date stored in a database, with no time and as such, timezone offset not applicable at all.

And even if time was in there, the machine that wrote to the database is in UTC. The machine where I am connecting to the database from is not, which I guess is the source of such evil....

[ferrao]

Just for clarification, defaults are not to use UTC:

node-postgres/lib/defaults.js

Line 56 in 06fbe19

parseInputDatesAsUTC: false,

[charmander]

The problem is not if the return value is a Date or a string.

I guess it isn’t if you want to distinguish the return value from the process of parsing, but that’s not what people have been talking about. The only sensible string return value is the form '2019-10-21', and that’s what it should be, instead of a Date.

[ferrao]

Going through the docs it states that date / timestamp / timestamptz are all converted into JavaScript objects, so for me that was expected and I had no issues with having to remove the time part of the string, if the days were correct... but they are not...

I can not apply such method to 2019-10-20T23:00:00.000Z, as I would end up with 2019-10-20 when the correct day is 2019-10-21.

But I understand what you are saying @charmander.

[abenhamdine]

see discussion here brianc/node-pg-types#50

[abenhamdine]

For anyone interested, this behaviour is planned to be changed in next version of pg-types (4.x), PR is merged here brianc/node-pg-types#121.
You can find the milestone here : https://github.com/brianc/node-pg-types/milestone/1

[ellenaua]

Solution - to override parser for date fields:

var types = require('pg').types
types.setTypeParser(types.builtins.DATE, (val) => val)

[robross0606]

Solution - to override parser for date fields:

var types = require('pg').types
types.setTypeParser(types.builtins.DATE, (val) => val)

Can you tell me what this is effectively doing? Is this returning the value parsed as a string? If so, in what format is the resulting date?

[rhazegh]

Solution - to override parser for date fields:

var types = require('pg').types
types.setTypeParser(types.builtins.DATE, (val) => val)

Can you tell me what this is effectively doing? Is this returning the value parsed as a string? If so, in what format is the resulting date?

@robross0606 It returns a YYYY-MM-DD string

[ohadfarkash]

It wasn't immediately obvious to me that array types are handled using a separate OID. The typname value for array types are the regular type name with _ prefix. So date[] was _date.
For anyone looking to override the existing parser for Date array types Date[]:

var types = require('pg').types
types.setTypeParser(1182, v => {
    v = v.substring(1, v.length - 1)
    return v.split(',')
})

This worked for me. Do let me know if there's a better way to handle the array using the internal array parser from pg-types.

[icon-ramico]

It wasn't immediately obvious to me that array types are handled using a separate OID. The typname value for array types are the regular type name with _ prefix. So date[] was _date. For anyone looking to override the existing parser for Date array types Date[]:

var types = require('pg').types
types.setTypeParser(1182, v => {
    v = v.substring(1, v.length - 1)
    return v.split(',')
})

This worked for me. Do let me know if there's a better way to handle the array using the internal array parser from pg-types.

pg.types.setTypeParser(1182, v => v.substring(1, v.length - 1).split(',')); //1182 for date[]

[markedwards]

For anyone interested, this behaviour is planned to be changed in next version of pg-types (4.x), PR is merged here brianc/node-pg-types#121. You can find the milestone here : https://github.com/brianc/node-pg-types/milestone/1

3 years later and the behavior is still to force "date" fields to a JS Date. Did this get reverted? Did it not happen?

This is not sane behavior.

[charmander]

@markedwards A new major version of pg-types has been released with the fix, but there hasn’t been a corresponding new major version of pg to depend on the new pg-types.