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

71–77 of 77 posts

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

#71

Post by fluoridation »

da_chicken wrote: post #69
ralferoo wrote: post #65

Earlier quoted context omitted.

It seems you're arguing based on the assumption that real time is exactly 365x24x60x60 seconds every year or exactly 366x24x60x60 seconds on leap years. It's not. The problem is that we have a very precise definition of a second in terms of decay of atoms (maybe precise is the wrong word, as it's the statistical likelihood of x atoms decaying given certain conditions, but whatever). The problem is arguably that this…

Because sometimes when you measure time you want to know what fraction of the day it is, and sometimes when you measure time you want an objective measure of the passage of time. Quite often, we want to use something originally recorded using the former to calculate the latter. It is most convenient to have a second that is of fixed duration. Which is kind of exactly why the tz database exists in the first place. Exc…

>it's not really defensible as a matter of being a factual description of reality

Is it a factual description of reality? "Seconds since epoch" is an almost entirely abstract idea, given that neither seconds nor epochs exist in the universe. The only way it's connected to reality is that time moves forwards, so "since" has meaning. So it seems to me that someone who says "seconds since epoch" can choose to give the words any meaning they like as long as everyone understands what is meant.

So to me this is as relevant a complaint as saying that the special case of graphs shouldn't be called "trees" because branches in real trees sometimes rejoin. It's a metaphor. We're dealing with entirely human-made concepts with barely any input from the real world, we can use any words we like.

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

#72

Post by ncruces »

goldfishgold wrote: post #40
nalgeon wrote: post #20

Earlier quoted context omitted.

[flagged]

There's nothing in the linked blog post justifying why you might want a higher precision time. In fact the blog post would benefit from an explanation that outlines the current datetimes in sqlite and why they're insufficient.

Internally, SQLite uses milliseconds since the Julian day epoch stored as an 64-bit integer for date math, and uses the proleptic gregorian calendar (no leap seconds).

Externally, it uses either (a subset of) ISO 8601 with millisecond precision for a textual representation, or a IEEE 64-bit float representing either days since the Julian day epoch, or seconds since the Unix epoch.

You can also use integer seconds since the Unix epoch, but you'll get outside the supported date range before you use 53-bits, so 64-bit floats are equally safe.

The supported date range, btw., is from JDN zero through 9999-12-31T23:59:59.999.

In the 21st century, both of these representations can store the correctly rounded millisecond.

SQLite accepts time zone offsets, but immediately converts all times to UTC (or local time) and does not store the offset, so date functions can only output UTC or local time (no other time zone or offset).

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

#73

Post by da_chicken »

fluoridation wrote: post #71
da_chicken wrote: post #69

Earlier quoted context omitted.

Because sometimes when you measure time you want to know what fraction of the day it is, and sometimes when you measure time you want an objective measure of the passage of time. Quite often, we want to use something originally recorded using the former to calculate the latter. It is most convenient to have a second that is of fixed duration. Which is kind of exactly why the tz database exists in the first place. Exc…

>it's not really defensible as a matter of being a factual description of reality Is it a factual description of reality? "Seconds since epoch" is an almost entirely abstract idea, given that neither seconds nor epochs exist in the universe. The only way it's connected to reality is that time moves forwards, so "since" has meaning. So it seems to me that someone who says "seconds since epoch" can choose to give the w…

It's only as abstract as "meters of distance". Which is to say, not in any practical sense until we introduce general or special relativity. The fact that it's synthetic and not an natural unit doesn't mean it's abstract or variable. It means it's arbitrary. It could be any value, but it must remain static.

That's also why we don't use the kings body dimensions as the standard of measurement anymore. We don't need to recalibrate the entire nation's standard of "foot," "inch," "cubit," or whatever when the king grows. That's not useful.

Just because the inch is the width of an adult man's thumb doesn't mean the purpose of the inch is to define thumbs and should always follow from that. That is not the goal of wanting the measuring unit. It's the same for seconds.

That's why I said: Sometimes you want to know what time of day it is, and sometimes you want to know how long something took to occur. That is to say, a datetime and a timespan. Now, you could use a different unit of measurement for those to, but in practical terms it's stupid.

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

#74

Post by fluoridation »

da_chicken wrote: post #73
fluoridation wrote: post #71

Earlier quoted context omitted.

>it's not really defensible as a matter of being a factual description of reality Is it a factual description of reality? "Seconds since epoch" is an almost entirely abstract idea, given that neither seconds nor epochs exist in the universe. The only way it's connected to reality is that time moves forwards, so "since" has meaning. So it seems to me that someone who says "seconds since epoch" can choose to give the w…

It's only as abstract as "meters of distance". Which is to say, not in any practical sense until we introduce general or special relativity. The fact that it's synthetic and not an natural unit doesn't mean it's abstract or variable. It means it's arbitrary . It could be any value, but it must remain static . That's also why we don't use the kings body dimensions as the standard of measurement anymore. We don't need…

Meters and seconds are both abstract and arbitrary. Abstract because time and space aren't divided. A measure of length isn't concrete like a count of atoms in a cup is. Surely we can agree that those two things have different degrees of concreteness. Yes, the phrase "a second" can be translated to a real equivalent, but the "second" is human idea, unlike the atom.

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

#75

Post by funny_falcon »

cryptonector wrote: post #57
funny_falcon wrote: post #51

Earlier quoted context omitted.

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.

What “Statically typed” would mean for SQL DB with extensible type system?

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

#76

Post by cryptonector »

funny_falcon wrote: post #75
cryptonector wrote: post #57

Earlier quoted context omitted.

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

What “Statically typed” would mean for SQL DB with extensible type system?

It means you can define new types, not that you can store values of arbitrary types in columns you already have.

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

#77

Post by da_chicken »

fluoridation wrote: post #74
da_chicken wrote: post #73

Earlier quoted context omitted.

It's only as abstract as "meters of distance". Which is to say, not in any practical sense until we introduce general or special relativity. The fact that it's synthetic and not an natural unit doesn't mean it's abstract or variable. It means it's arbitrary . It could be any value, but it must remain static . That's also why we don't use the kings body dimensions as the standard of measurement anymore. We don't need…

Meters and seconds are both abstract and arbitrary. Abstract because time and space aren't divided. A measure of length isn't concrete like a count of atoms in a cup is. Surely we can agree that those two things have different degrees of concreteness. Yes, the phrase "a second" can be translated to a real equivalent, but the "second" is human idea, unlike the atom.

Distance can absolutely be concrete. We can specify distance traveled by light in a vacuum, or, if you'd rather, a specified number of Planck units. Similarly, time can be defined by the number of wavelengths of a photon of a given energy level. Which is basically the definition of modern SI second and meter are derived. They're based on the speed of light.

In the end, all you're doing is finding different ways to dissect physical constants. But you're still using those constant. The constant itself doesn't change because the Earth revolves slower today than it did yesterday, yet the planet certainly does revolve the same distance about its axis in more time.

Which is why I say distance and time are not really variable or imprecise (better terms than abstract, which simply has to many nebulous meanings) until you start to introduce relativity.

In fact, time is a fundamental element of many physical constants. The Planck constant, the gravitational constant, and the speed of light are all fundamentally based on time and distance. And since the speed of light relates to mass and energy, whatever units you pick need to be consistent. If we say that 1 second is always 1/84,000th of a solar Earth day (instantaneously I suppose?) then you've immediately made 3 of the known physical constants depending on how quickly the Earth spins. And you could do that and recalculate all of physics every day if you wanted to. But it's kind of stupid. It would be like re-graduating a tape measure every day you build a house. In theory it's just fine. Practically it's really not.

Post reply on HN