Hacker News Reader

Comment by metadat | Hacker News Reader

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?

View on HN

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

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.

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.

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.

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?

By definition unix timestamps are UTC. Python's datetime module has a lot of footguns because naive timestamps can represent UTC, local time, or even a non-standard application-specific time. Python's aware timestamps just carry the tzinfo information along with the naive timestamp so that you can get to UTC from the abstract naive timestamp. It's a really overdesigned footgun of Python's datetime module more than anything, but again not unix timestamps which are always an offset from 1970 midnight UTC.

Cool, thanks for the clarification!

You can do it in a consistent way, for example always store the timestamp at 00:00 UTC. But that's not obvious just by looking at the values, which is why it can be ambiguous.

And when parsing those values, you could do for example (JS) `new Date(ts*1000).getDay()`. Of course that's not correct - you need to use the UTC methods. But it's easy to miss, and may pass all your tests until you switch the timezone.

Those aren't massive issues - you just have to be careful with the parsing and serialization, and you need to be careful in any case. It just explains my personal preference.

Not sure I follow. What's the difference between `new Date(ts).getDay()` when ts is an ISO8601 string or a ms since UNIX epoch value. Pretty sure the result is the same and whether you need to use `getDay` or `getUTCDay` is a display issue that affects both variants.

I could see it being more convenient if you're just dumping the raw values without any transformation or you don't know what data type is stored in a column, but otherwise the difference seems largely one of taste and not so much relative error rates?

when you do that, it will give you different days depending on your timezone. 1701388800 is 2023-12-01 in london, but still 2023-11-30 on new york.

Sure, but that’s also true for an iso8601 string unless you’re extracting things directly. But also you’re assuming that your iso8601 string is in local time which it may not be (eg string generated in London but accessed in New York). Localizing things to be time zone aware in a way that meets user expectations is hard because it’s a squishy domain specific UX problem, not a technical one so arguing about it like a technical issue feels like a wrong approach.

You're right that you'll have exactly the same issues if you use Date with a string.

However, with a string date you can bypass the Date class completely in many use cases.

My experience with this is anecdotal, but I've ran into many bugs due to conversion between day-precision values and Date objects. One example was a date picker library that returned values at 00:00 in the local timezone instead of UTC, which were then not correctly converted to UTC-based values before being persisted.

Once again, these are all preventable issues - just use UTC everywhere. I've just seen issues like that happen enough that I prefer never converting between dates and timestamps if I can avoid it.

> My experience with this is anecdotal, but I've ran into many bugs due to conversion between day-precision values and Date objects. One example was a date picker library that returned values at 00:00 in the local timezone instead of UTC, which were then not correctly converted to UTC-based values before being persisted.

Oh man, that sounds ugly, and an invitation for disaster. Lots of off-by-ones.

In my project's case, I also need to do math with them for analytical purposes. It's a game collection manager, so it's tracking the day you bought, beat, and 100%d a game.

I derive how long it took to beat a game by subtracting the UNIX timestamp of the purchase date from the beaten date's timestamp, then divide by (60*60*24) to measure days. It's handy to show games in the backlog and sort by purchase date, too, so you can target the games that've been sitting the longest. There are plans to graph out a collection in terms of its path from new to beaten to completed in a Gantt-style chart or something else fun.

But yeah, I just thought to ask because in JSON I and others can read `2023-12-01`, but not `1701417600`. SQLite and Python can do conversions for the calculations, and Python even has the timedelta module that might make my comparisons easier to do or slightly more accurate.

Edit: asterisks

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).

SQLite has five types, documented here: 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

> 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

[1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...

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

The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.

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.

Reply on news.ycombinator.com