[sltkr]

This library doesn't deal with the notion of local time at all. It's all UTC-based times, possibly with a user-supplied timezone offset, but then the hard part of calculating the timezone offset must be done by the caller.

I do think the documentation could be a little clearer. The author talks about “time zones” but the library only deals with time zone offsets. (A time zone is something like America/New_York, while a time zone offset is the difference to UTC time, which is -14400 seconds for New York today, but will be -18000 in a few months due to daylight saving time changes.)

[Someone]

> It's all UTC-based times

Not even that. UTC has leap seconds, which this code doesn’t handle (FTA: “The calendrical calculations always assume a Gregorian calendar, with no leap seconds”)

It copies that from the golang time package, which makes the same claim (https://pkg.go.dev/time)

That makes life a lot simpler for the implementer, but doesn’t that mean you can only reliably use these two libraries for computing with durations, not with moments in time or vice versa? The moment you start mapping these times to real world clocks and adding durations to them, you run the risk of getting small (up to about half a minute, at the moment) inconsistencies.

[nottorp]

> The calendrical calculations always assume a Gregorian calendar, with no leap seconds.

That could drift a bit if you can represent a 580 million year interval (with millisecond precision), wouldn't it?

[Someone]

A library that handles leap seconds can’t do much better, as it’s as good as impossible to predict leap seconds, certainly not that far ahead.

Luckily, leap seconds are on the way out (https://en.wikipedia.org/wiki/Leap_second#International_prop...)

[doctorpangloss]

Another POV is, why build for the SQLite "ecosystem" at all?

[devmor]

Probably because SQLite is one of (if not THE most) widely used database implementations for IoT software in the world.

That’s like asking why someone made a package for javascript.

[ErikBjare]

SQLite is the most widely used database in the world. Full stop.

[raggi]

after a few things like hosts files which exist on every major operating system including windows

[vsuperpower2021]

You can find dozens of SQlite databases on a single phone if you want to be pedantic

[nalgeon]

Thanks for the suggestion! True, only fixed offsets are supported, not timezone names.

[alberth]

@nalgeon

Do you plan to address the use cases in the SO post, or asked differently - what is the intended use case of this library?

I tried to recreate it on your site (which is very cool btw in allowing the code to run in browser) and it seems to fail and give the wrong time difference.

  select time_compare(time_date(1927, 12, 31, 23, 58, 08, 0, 28800000), time_date(1927, 12, 31, 23, 58, 09, 0, 28800000));

Results in an answer of '1', which is incorrect.

Please don't take my comments as being negative or unappreciated, this is super difficult stuff and anyone who tries to make the world an easier place should be thanked for that. So thank you.

----

EDIT: this post explains why the answer isn't "1"

https://stackoverflow.com/questions/6841333/why-is-subtracti...

[nalgeon]

I appreciate your comments, and thank you for trying out the extension.

This query returns -1 (minus one, not one), which seems correct to me. The first date is before the second:

    select time_compare(
      time_date(1927, 12, 31, 23, 58, 08, 0, 28800000),
      time_date(1927, 12, 31, 23, 58, 09, 0, 28800000)
    );

    -1

[kaoD]

As discussed in the top-level comment, this library has no concept of timezones (only offsets) so the SO link does not apply. The time rollback only happened in Asia/Shanghai.

[akira2501]

> A time zone is something like America/New_York

It's US/Eastern. Paul Eggert can call this a "deprecated compatibility time" all he wants, but "Eastern Time Zone" is the official name of the time zone as maintained by the civil time keeping authority.

[michaelcampbell]

If IANA is fine with it, that's fine with me too.