5 ms·

I dig it, super cool idea! What about timestamps, though? SQLite doesn't support them.. does it end up being restricted to the lowest common denominator betwe

I dig it, super cool idea! What about timestamps, though? SQLite doesn't support them.. does it end up being restricted to the lowest common denominator between the two databases in terms of supported types/functionality?

saurik 3y ago
SQLite has a number of functions to work with time but the underlying storage format is just going to be--depending on what precision/range of timestamp you want--a float or an integer... to the extent, of course, that SQLite has types at all (as it frankly doesn't, thereby making this question kind of moot).
- simonw 3y ago
  SQLite has five types, documented here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html null, integer, real, text, blob It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) but that changed in November 2021 with the release of strict table mode in version 3.37.0: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
  - bch 3y ago
    > It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) Richard Hipp (inventor, principal dev of SQLite) called it “manifest typing”[0], indeed likely inspired by Tcl[1], of which he is a Core Team member emeritus, and of which SQLite was initially born, as a loadable Tcl package. [0] https://www.sqlite.org/different.html https://www.sqlite.org/different.html [1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?R=0&O=EIAS https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...
  - o11c 3y ago
    JSON is a sixth type in practice due to the way `text` acts differently depending on whether it is fed directly from one JSON function to another. See https://sqlite.org/json1.html#value_arguments https://sqlite.org/json1.html#value_arguments
    - kobieps 3y ago
      The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.
  - saurik 3y ago
    Ah! I mean, only two years old, but OK ;P. Regardless: my point stands that you can trivially store and work with timestamps in SQLite as they are simply a real or integer or text; like, the idea that you need a special timestamp data type or you can't work with such values in an engine that only two years ago got support for checking the types at all and encourages you to just write code is highly strange.
ochiba 3y ago
Co-founder (Conrad) here. SQLite does indeed have a narrow set of types. Timestamps in Postgres are converted to text in SQLite in a format that is compatible with ISO8601 and SQLite's functions. Type conversions are documented here: https://docs.powersync.com/usage/sync-rules/types https://docs.powersync.com/usage/sync-rules/types
- zlg_codes 3y ago
  I see you're using ISO8601 and including timezone data, which seems best for generalizing. Somewhat related: if you were storing UNIX timestamps that only need the resolution of a day and will always be UTC, would you use an integer for the UNIX time or just a shortened ISO8601 without H:M:S data? I've been trying to decide on this for a feature in a tool I'm making. In either case, SQLite has the ability to convert back and forth, so it'd really be the exported JSON containing the data whose readability would be affected more than anything.
  - matharmin 3y ago
    Personally I prefer the shortened ISO8601 format (YYYY-MM-DD). While it's not the most efficient, efficiency doesn't often matter on that level. It's unambiguous, can be parsed by practically any library, and easily human-readable. I'd avoid using unix timestamps for day-resolution, since it's very easy to make a mistake with the timezone when parsing, which you only notice when changing between a positive and negative timezone. Julian day is another option, but support for it is not quite as universal. If efficiency is important, that's the format I'd use.
    - vlovich123 3y ago
      What’s the ambiguity issue with Unix time stamps? Unix time stamps are expressed as a UTC offset always so I’m not sure how time zones come into play / what parsing might involved.
      - zlg_codes 3y ago
        I think he's referring to behavior that some software has when it encounters raw UNIX timestamps without TZ information. I recall seeing a change like that in Python's datetime module as of late, so that even if UNIX timestamps are UTC by default, it's more explicit. In the case of my project, the goal is to have UTC-only timestamps/dates, and since the resolution I'm using it for is a calendar day, time zone doesn't matter. I will still need to be explicit about it in my code when converting. Or maybe edge cases like historical timezone changes, leap seconds?

saurik 3y ago

SQLite has a number of functions to work with time but the underlying storage format is just going to be--depending on what precision/range of timestamp you want--a float or an integer... to the extent, of course, that SQLite has types at all (as it frankly doesn't, thereby making this question kind of moot).

simonw 3y ago
SQLite has five types, documented here: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html null, integer, real, text, blob It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) but that changed in November 2021 with the release of strict table mode in version 3.37.0: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- bch 3y ago
  > It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) Richard Hipp (inventor, principal dev of SQLite) called it “manifest typing”[0], indeed likely inspired by Tcl[1], of which he is a Core Team member emeritus, and of which SQLite was initially born, as a loadable Tcl package. [0] https://www.sqlite.org/different.html https://www.sqlite.org/different.html [1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?R=0&O=EIAS https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...
- o11c 3y ago
  JSON is a sixth type in practice due to the way `text` acts differently depending on whether it is fed directly from one JSON function to another. See https://sqlite.org/json1.html#value_arguments https://sqlite.org/json1.html#value_arguments
  - kobieps 3y ago
    The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.
- saurik 3y ago
  Ah! I mean, only two years old, but OK ;P. Regardless: my point stands that you can trivially store and work with timestamps in SQLite as they are simply a real or integer or text; like, the idea that you need a special timestamp data type or you can't work with such values in an engine that only two years ago got support for checking the types at all and encourages you to just write code is highly strange.

ochiba 3y ago

Co-founder (Conrad) here. SQLite does indeed have a narrow set of types. Timestamps in Postgres are converted to text in SQLite in a format that is compatible with ISO8601 and SQLite's functions. Type conversions are documented here: https://docs.powersync.com/usage/sync-rules/types https://docs.powersync.com/usage/sync-rules/types

zlg_codes 3y ago
I see you're using ISO8601 and including timezone data, which seems best for generalizing. Somewhat related: if you were storing UNIX timestamps that only need the resolution of a day and will always be UTC, would you use an integer for the UNIX time or just a shortened ISO8601 without H:M:S data? I've been trying to decide on this for a feature in a tool I'm making. In either case, SQLite has the ability to convert back and forth, so it'd really be the exported JSON containing the data whose readability would be affected more than anything.
- matharmin 3y ago
  Personally I prefer the shortened ISO8601 format (YYYY-MM-DD). While it's not the most efficient, efficiency doesn't often matter on that level. It's unambiguous, can be parsed by practically any library, and easily human-readable. I'd avoid using unix timestamps for day-resolution, since it's very easy to make a mistake with the timezone when parsing, which you only notice when changing between a positive and negative timezone. Julian day is another option, but support for it is not quite as universal. If efficiency is important, that's the format I'd use.
  - vlovich123 3y ago
    What’s the ambiguity issue with Unix time stamps? Unix time stamps are expressed as a UTC offset always so I’m not sure how time zones come into play / what parsing might involved.
    - zlg_codes 3y ago
      I think he's referring to behavior that some software has when it encounters raw UNIX timestamps without TZ information. I recall seeing a change like that in Python's datetime module as of late, so that even if UNIX timestamps are UTC by default, it's more explicit. In the case of my project, the goal is to have UTC-only timestamps/dates, and since the resolution I'm using it for is a calendar day, time zone doesn't matter. I will still need to be explicit about it in my code when converting. Or maybe edge cases like historical timezone changes, leap seconds?