4 ms·

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

by sltkr 2y ago

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

nalgeon 2y ago
Thanks for the suggestion! True, only fixed offsets are supported, not timezone names.
- alberth 2y ago
  @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-subtracting-these-two-epoch-milli-times-in-year-1927-giving-a-strange-r https://stackoverflow.com/questions/6841333/why-is-subtracti...
  - nalgeon 2y ago
    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 2y ago
    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.
Someone 2y ago
> 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 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.
- doctorpangloss 2y ago
  Another POV is, why build for the SQLite "ecosystem" at all?
  - devmor 2y ago
    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 2y ago
      SQLite is the most widely used database in the world. Full stop.
      - raggi 2y ago
        after a few things like hosts files which exist on every major operating system including windows
        vsuperpower2021 2y ago
        You can find dozens of SQlite databases on a single phone if you want to be pedantic
akira2501 2y ago
> 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 2y ago
  If IANA is fine with it, that's fine with me too.

by sltkr 2y ago

nalgeon 2y ago

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

alberth 2y ago
@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-subtracting-these-two-epoch-milli-times-in-year-1927-giving-a-strange-r https://stackoverflow.com/questions/6841333/why-is-subtracti...
- nalgeon 2y ago
  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 2y ago
  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.

Someone 2y ago

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

doctorpangloss 2y ago
Another POV is, why build for the SQLite "ecosystem" at all?
- devmor 2y ago
  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 2y ago
    SQLite is the most widely used database in the world. Full stop.
    - raggi 2y ago
      after a few things like hosts files which exist on every major operating system including windows
      - vsuperpower2021 2y ago
        You can find dozens of SQlite databases on a single phone if you want to be pedantic

akira2501 2y ago

> 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 2y ago
If IANA is fine with it, that's fine with me too.