Why does JSONB show a different date format than Javascript?

Question

With Javascript's .toISOString(); and .toJSON() I get

(new Date()).toISOString();
2018-01-30T12:04:13.153Z

Using PostgreSQL when I return a date, I get

# SELECT to_jsonb('2018-01-30T12:04:13.153Z'::timestamp with time zone);
            to_jsonb             
---------------------------------
 "2018-01-30T06:04:13.153-06:00"
(1 row)

Why are these two different formats?

Answer 1

With ECMA-262 v6, the Z must be present in the result of .toISOString if a time zone is provided, you do not need millisecond precision. Javascript on the other hand has no date types to begin with. Both of these formats are ISO 8601. PostgreSQL doesn't specify a Z and it gives only the precision provided. Nothing prohibits that.

The Date() constructor will gladly accept both formats, and you should be using Moment.js for all your date time needs in Javascript. You should be immediately constructing date objects with the output of PostgreSQL. This may not be done for you, it also may be something you have to do as JSON doesn't know the difference from a ISO 8601 date/time object, and a string that looks exactly like it. From that point, it's out of PostgreSQL's hand. All that PostgreSQL ensures is that the output is valid JavaScript Date() input, which is above and beyond because the JSON spec doesn't even require that.

ECMA-262 v6 spec,

The ECMA-262 v6 spec says,

Date.prototype.toISOString() This function returns a String value representing the instance in time corresponding to this time value. The format of the String is the Date Time string format defined in 20.3.1.16. All fields are present in the String. The time zone is always UTC, denoted by the suffix Z**. If this time value is not a finite Number or if the year is not a value that can be represented in that format (if necessary using extended year format), a RangeError exception is thrown.

That says this,

Z is the time zone offset specified as "Z" (for UTC) or either "+" or "-" followed by a time expression HH:mm

This format includes date-only forms:

YYYY
YYYY-MM
YYYY-MM-DD

It also includes “date-time” forms that consist of one of the above date-only forms immediately followed by one of the following time forms with an optional time zone offset appended:

THH:mm
THH:mm:ss
THH:mm:ss.sss

JSON Spec

The JSON spec says nothing about date formatting. In fact, there isn't even a date type mentioned. This is probably because Javascript dates are objects not primitives.

PostgreSQL

PostgreSQL outputs only the minimum precision provided to the constructor, and without the z (still in ISO 8601).

SELECT to_jsonb(dt::timestamp with time zone)
FROM ( VALUES
  ('2018-02-01T13:24:36.466849-06:00'),
  ('2018-02-01T13:24:36-06:00');