SQLite-jiff: SQLite extension for timezones and complex durations | Hacker News Reader

SQLite-jiff: SQLite extension for timezones and complex durations

github.com

94 pointsingve17 comments

What I'd find really interesting in this space, would be coalescing around a "standard" storage format and API.

This definitely fills a need [1], and I'd easily port something like this to my Go bindings as needed (no Rust necessary).

It's encouraging that the storage format is a (recently published) RFC 9557 [2]. Not so sure about having jiff on all the function names.

Making SQLite easy to extend (Alex's Rust FFI, the APSW Python bindings, etc) really is a treasure trove.

1: https://news.ycombinator.com/item?id=41043499

2: https://datatracker.ietf.org/doc/rfc9557/history/

"RFC 9557: Date and Time on the Internet: Timestamps with Additional Information" https://www.rfc-editor.org/rfc/rfc9557.html

  1996-12-19T16:39:57-08:00[America/Los_Angeles]

> The offset -00:00 is provided as a way to express that "the time in UTC is known, but the offset to local time is unknown"

  1996-12-19T16:39:57-00:00
  1996-12-19T16:39:57Z

> Furthermore, applications might want to attach even more information to the timestamp, including but not limited to the calendar system in which it should be represented.

  1996-12-19T16:39:57-08:00[America/Los_Angeles][u-ca=hebrew]

  1996-12-19T16:39:57-08:00[_foo=bar][_baz=bat]

Astropy supports the Julian calendar – circa Julius Caesar (~25BC), born by Caesarean section (an Eastern procedure)), and also astronomical numbering which has a Year Zero. [1]

There is still not a zero in Roman numerals; there's "nulla" but no zero. Modern zero is notated with the Arabic numeral 0.

[1] Year Zero, calendaring systems: https://wrdrd.github.io/docs/consulting/knowledge-engineerin...

astropy.time > Time Formats: https://docs.astropy.org/en/latest/time/#time-format

Which calendar is the oldest?

List of calendars: https://en.wikipedia.org/wiki/List_of_calendars

Epoch > Calendar eras: https://en.wikipedia.org/wiki/Epoch#Calendar_eras

Are string tags at the end of the datetime that indexable?

Shouldn't there be #LinkedData URLs or URIs in an RDFS vocabulary for IANA/olsen and also for calendaring systems?

E.g. Schema.org/dateCreated has a range of schema.org/DateTime, which supports ISO8601, which also specifies timezone Z (but not -00:00, as the RFC mentions).

Astounding that there's been no solution for calendar year date offsets on computers. Are there notations for indicating which system, or has everyone on earth also always assumed that bare integer years are relative to their preferred system?

Somewhere there's a chart of how recorded human history is only like 10K out of 900K (?) years of hominids of earth, through ice ages and interglacials like the Holocene.

Not sure why someone should use this; adding value-transforming functions is trivial in sqlite. Here's what's being proposed instead:

1) poc solution without unit-tests whatsoever

2) bugs like "milisecond" instead of "millisecond" due to lack of purpose

3) no error handling ("todo") or panics in sqlite (due to unwrap)

4) advocates for enabling load_extension, which makes applications vulnerable

This isn't a complete extension, as mentioned in the README several times. I wanted a poc to use the new jiff library in SQL functions to test if it works. It did, so I put it in a repo for others to see. Definitely not production ready, it will change a lot in the future.

Also one note: load_extension isn't required, you can statically compile this and other extensions into an application. That being said, load_extension itself doesnt make your application 'vulnerable.' SQLite offers many features to limit and control dynamically loadable extensions, like sqlite3_enable_load_extension() and SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION. Of course it depends what your definition of "security" is, but load_extension() doesn't need to be dangerous.

I hope, someone collects all these extensions and tests the combination very agressively :D

I guess I like the idea but this would need to be a core feature in C and not a rust extension to be useful

Why is this not useful as an extension, Rust or otherwise?

Some builds of SQLite (e.g. the default macOS version) don't allow extension loading. Local policies may equally disallow extension loading.

(I think it's definitely useful as an extension but I can appreciate there's situations where a core feature is easier.)

Agreed, though the topic of timezone+DST support has come up a few times in the SQLite forums, and the SQLite team has never expressed much interest in it. Which I don't blame them, they would need to write it from scratch in C (for licensing reasons), and keep a timezone DB updates, which sounds like a nightmare.

I'm curious - do you find yourself constrained by the default macOS build often? Typically I `brew install sqlite` and use that CLI to get around extension loading issues (and for more modern SQLite versions). Same with Python's default MacOS build, which I avoid at all costs. Though very curious to hear more about times where that isn't a viable option

> do you find yourself constrained by the default macOS build often?

Only for about 10 minutes after a new install until I ...

> `brew install sqlite`

But I could understand that some people may not have that option (although I've yet to work at a place that locks macOS down that much, I've worked at places which locked down their Windows laptops down far more than that.)

Often SQLite is what you will get, quite anywhere. But you might not be able to install so easily extensions.

Also a key value of SQLite is to be quite self contained and easy to build without strong dependency.

Finally, I'm not an expert of SQLite extensions, but I think that you will easily have a strong impact on performance using the extension for core type that you might use a lot in big tables.

Even though it's written in Rust, you can still use it in other languages like Python or Node.js. It compiles to a shared library file, which most SQLite clients support with `.loadExtension()` or another similarly named method.

This extension isn't the best example, since it's a thrown-together demo, but sqlite-ulid is a similar extension written in Rust that could be run anywhere, not just Rust

That being said, writing in Rust instead of C has many drawbacks (slightly slower, cross compiling, larger binary sizes, WASM is more difficult, statically compiling is complex, etc.). But for cases like this, many SQLite extensions I write in Rust are just light wrappers around extremely high quality Rust crates (like jiff), which makes my life easier and it "good enough"

> slightly slower

Because of the FFI overhead?

I think so - I wrote my own Rust FFI bindings for SQLite extensions, and I tried to make it as fast as possible, but a "hello world" extension was still 10-15% slower in Rust than C[0].

That being said, it depends what the extension does - a "hello world" extension mainly just calls the same SQLite C APIs over and over again, so the small Rust layer makes it a bit slower. However, my Rust extensions for regex[1] and CSV parsing[2] are usually faster than the C counterparts, mostly due to less memory allocations and batching. It's not a 1:1 comparison (both extensions have slightly different APIs and features), but I'd say a lot of "real world" features available in Rust can be faster than what's available in C.

That being said, I'm sure someone could write their own faster CSV or regex extension in C that is faster than the Rust ones. But it's a ton of work to do that from scratch, and I find wrapping a pre-existing Rust implementation to be much easier

[0] https://github.com/asg017/sqlite-loadable-rs?tab=readme-ov-f... [1] https://github.com/asg017/sqlite-regex [2] https://github.com/asg017/sqlite-xsv

First, Rust compiled is not wt that performant. Well behind c, go, c++,...

But also because SQLite is based on crazily optimized C!

Why would WASM be any more difficult in Rust than in C?

Because of SQLite. The SQLite official WASM build is a very complicated emscripten build, so if you want to write a SQLite extension for the WASM build, you need to statically compile it into the complex emscripten build process. This is hard to do in general, but especially for Rust

That being said, I find WASM projects that are written entirely in Rust to be pleasant, more pleasant than C WASM projects. But for SQLite extensions specifically, Rust/WASM gets a bit harder

Reply on news.ycombinator.com