Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: High-precision date/time in SQLite

antonz.org

Post reply on HN
Print view View original

51–60 of 77 posts

Re: Show HN: High-precision date/time in SQLite

#51

Post by funny_falcon »

cryptonector wrote: post #29

I so wish that SQLite3 had an extensible type system.

As a PostgreSQL smallish contributor I just can say: NO, DON'T DO THIS!!!!

Extensible type system is a worst thing that could happend with database end-user performance. Then one may not short-cut no single thing in query parsing and optimization: you must check type of any single operand, find correct operator implemenation, find correct index operator family/class and many more all through querying system catalog. And input/output of values are also goes through the functions, stored in system catalog. You may not even answer to "select 1" without consulting with system catalog.

There should be sane set of builtin types + struct/json like way of composition. That is like most DBs do except PostgreSQL. And I strongly believe it is right way.

Re: Show HN: High-precision date/time in SQLite

#52

Post by kaoD »

alberth wrote: post #44
nalgeon wrote: post #24

Earlier quoted context omitted.

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

@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…

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.

Re: Show HN: High-precision date/time in SQLite

#53

Post by kaoD »

davidhyde wrote: post #2

I think it’s important to be explicit about whether or not signed integers are used. From reading the document it seems that they may be signed but they could not be. If they are signed then you could have multiple bit strings that represent the same date and time which is not great.

> multiple bit strings that represent the same date and time

How so?

Re: Show HN: High-precision date/time in SQLite

#54

Post by fluoridation »

edflsafoiewq wrote: post #48
fluoridation wrote: post #41

Earlier quoted context omitted.

It's unclear if that's what they're complaining about, but if it is, the reason people just say "seconds since epoch" instead of saying what they actually mean is precisely because no one wants to get into the weeds of leap seconds. POSIX timestamps are a convenient format and they represent an idealized "second" of constant length that's in a uniform correspondence with the rotation of the Earth. Most likely if you'…

No, that is literally exactly what they just complained about. > And it's annoying that you need to read some footnote to figure out what exactly it means; it's annoying that it is basically a code-phrase that you just need to know that it's not supposed to be taken literally. Even to point of deferring the real explanation to a secondary paragraph.

I mean if they're referring specifically to leap seconds or to some other obscure complexity about dealing with real time.

Re: Show HN: High-precision date/time in SQLite

#55

Post by doctorpangloss »

Someone wrote: post #49
sltkr wrote: post #17

Earlier quoted context omitted.

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…

> 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 comput…

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

Re: Show HN: High-precision date/time in SQLite

#56

Post by akira2501 »

sltkr wrote: post #17
alberth wrote: post #14

Does this handle the special case of timezone changes (and local time discontinuity) that Jon Skeet famously documented? https://stackoverflow.com/questions/6841333/why-is-subtracti... And computerphile explains so well in their 10-min video: https://www.youtube.com/watch?v=-5wpm-gesOY --- I've long ago learned to never build my own Date/Time nor Encryption libraries. There's endless edge cases that can bite you hard…

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

Re: Show HN: High-precision date/time in SQLite

#57

Post by cryptonector »

funny_falcon wrote: post #51
cryptonector wrote: post #29

I so wish that SQLite3 had an extensible type system.

As a PostgreSQL smallish contributor I just can say: NO, DON'T DO THIS!!!! Extensible type system is a worst thing that could happend with database end-user performance. Then one may not short-cut no single thing in query parsing and optimization: you must check type of any single operand, find correct operator implemenation, find correct index operator family/class and many more all through querying system catalog.…

> you must check type of any single operand, find correct operator implemenation, find correct index operator family/class and many more all through querying system catalog.

Not with static typing.

The problem with PG is that it's not fully statically typed internally. SQLite3 is worse still, naturally. But a statically typed SQL RDBMS should be possible.

Re: Show HN: High-precision date/time in SQLite

#58

Post by davidhyde »

kaoD wrote: post #53
davidhyde wrote: post #2

I think it’s important to be explicit about whether or not signed integers are used. From reading the document it seems that they may be signed but they could not be. If they are signed then you could have multiple bit strings that represent the same date and time which is not great.

> multiple bit strings that represent the same date and time How so?

You’re right, whether or not the integers are signed has nothing to do with the issue above. Unsigned integers have the same issue.

Here is an example for signed integers.

These represent zero time but have different representations in memory:

Seconds: 2 Nanoseconds: -2,000,000,000 (fits in a 32 bit number) Time: zero seconds

Seconds: -2 Nanoseconds: 2,000,000,000 Time: zero seconds

Here is an example for unsigned: Seconds: 1 Nanoseconds: 0 Time: 1 second

Seconds: 0 Nanoseconds: 1,000,000,000 Time 1 second

Re: Show HN: High-precision date/time in SQLite

#59

Post by kaoD »

davidhyde wrote: post #58
kaoD wrote: post #53

Earlier quoted context omitted.

> multiple bit strings that represent the same date and time How so?

You’re right, whether or not the integers are signed has nothing to do with the issue above. Unsigned integers have the same issue. Here is an example for signed integers. These represent zero time but have different representations in memory: Seconds: 2 Nanoseconds: -2,000,000,000 (fits in a 32 bit number) Time: zero seconds Seconds: -2 Nanoseconds: 2,000,000,000 Time: zero seconds Here is an example for unsigned: S…

Thanks, but I'm not gonna pretend that was my point. Dumb question from me, I just forgot the context that time was a pair of integers and was utterly confused, haha. You're spot on!

Re: Show HN: High-precision date/time in SQLite

#60

Post by devmor »

doctorpangloss wrote: post #55
Someone wrote: post #49

Earlier quoted context omitted.

> 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 comput…

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

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.

Post reply on HN