pg timestamp/timestamptz should be mapped to ISO-8601 compatible string

[ImSingee]

Currently, the timestamptz is mapped to YYYY-MM-DD hh:mm:ss.sssZ format and timestamp is mapped to YYYY-MM-DD hh:mm:ss.sss.

The document says "This is compatible with ISO8601 and SQLite’s functions.".

However, this format (use a space instead of T to separate date and time) isn't compatible with ISO-8601. (From Wikipedia, Separating date and time parts with other characters such as space is not allowed in ISO 8601, but allowed in its profile RFC 3339.)

Additionally, SQLite supports using T as separator:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

So, I think, powersync should switch to use T as separator instead of the space.

[rkistner]

You're right that the space is not actually allowed in ISO 8601. By contrast, RFC 3339 does allow it, even though it is specified as a profile/subset of ISO 8601, which makes things confusing.

Either way, we initially went with that format to be close to the defaults of how SQLite generates the values. But you're right that the format with the T separator would be better. We can't directly change it now without breaking backwards-compatibility, but we'll look into making this configurable, and perhaps change the default in the next major version.