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

Looking Forward to Postgres 19: It's About Time

pgedge.com

Post reply on HN
Print view View original

11–20 of 44 posts

Re: Looking Forward to Postgres 19: It's About Time

#11

Post by evdubs »

quotemstr wrote: post #10
evdubs wrote: post #6

> Recently, a new type of question has entered the database arena: what did this data look like last Tuesday? This question has been answerable in Dolt for years now.

This kind of DB isn't really answering this question. There's a lot more subtlety to time-span analysis than snapshotting. In particular, aligning two series is non-obvious. Say you have one time series with CPU-core task switches: T=1 task=A T=3, task=B, T=5 task=A, ... ... and another of CPU frequency changes ... T=2 freq_hz=800, T=5 freq_hz=1200, T=6 freq_hz=900 How, in SQL, do you express the question "How many C…

The cool thing about Dolt is that you [eventually] get the features of the databases (MySQL, PostgreSQL, SQLite, MongoDB) they emulate, so you can have your PG 19 temporality features as well as branching and merging.

Re: Looking Forward to Postgres 19: It's About Time

#12

Post by quotemstr »

evdubs wrote: post #11
quotemstr wrote: post #10

Earlier quoted context omitted.

This kind of DB isn't really answering this question. There's a lot more subtlety to time-span analysis than snapshotting. In particular, aligning two series is non-obvious. Say you have one time series with CPU-core task switches: T=1 task=A T=3, task=B, T=5 task=A, ... ... and another of CPU frequency changes ... T=2 freq_hz=800, T=5 freq_hz=1200, T=6 freq_hz=900 How, in SQL, do you express the question "How many C…

The cool thing about Dolt is that you [eventually] get the features of the databases (MySQL, PostgreSQL, SQLite, MongoDB) they emulate, so you can have your PG 19 temporality features as well as branching and merging.

Yep. I'm just pointing out that the problems Dolt solves are different from the problems a timeline-aware SQL algebra solves.

Re: Looking Forward to Postgres 19: It's About Time

#13

Post by larsnystrom »

throwaway7783 wrote: post #9
larsnystrom wrote: post #5

Why are they storing a time period (start and end date) in the first example? Why not just store the date when the price comes into effect? That would make both overlaps and time travel impossible without using any constraints.

Works when there is always an active price. Having an explicit end date allows certain rows to be inactive automatically after validity period. Think of seasonal categories/products etc which dont exist after a specific period

You could also make the price column nullable and just insert a row with price null and the date from which there should be no price.

Re: Looking Forward to Postgres 19: It's About Time

#14

Post by larsnystrom »

quotemstr wrote: post #8
larsnystrom wrote: post #5

Why are they storing a time period (start and end date) in the first example? Why not just store the date when the price comes into effect? That would make both overlaps and time travel impossible without using any constraints.

It's a trade-off. If you store both endpoints you can continue to think of rows as order-invariant tuples. If you store only one endpoint, you have to impose a meaningful order on the rows in order for them to make sense.

Sure, from a theoretical perspective, but in practice there’s got to be some sort of order at some point even when storing timespans.

Re: Looking Forward to Postgres 19: It's About Time

#15

Post by IgorPartola »

This is something that is incredibly useful. I built a system like this a while back that also adds versioning to each time period. The use case is this: let’s say you are tracking your state’s sales tax rate. You do not control this and data entry is manual so it is error prone. The rate is updated typically annually but sometimes more frequently.

Let’s say for 2026 you have it at 7.25% and you entered that into the system ahead of time (say December 2025). Today, June 12 you learn that it should have been 7.35%. It would be incorrect to say that the rate changed today: it was 7.35% since January 1. But you also don’t want to lose the fact that all your invoices have been generated using the wrong rate because if you go to recalculate them you will get a different answer.

In this case what you do is create version 2 of the rate in your database with the same time period but the correct rate. This would allow your other database objects to reference either version 1 or 2 and to even recalculate all the objects that reference version 1 to now reference version 2 such that you can get line item corrections and figure out what to do about them.

It is cumbersome to use but for the specific use case of modeling real world laws that are not available as machine-readable info it is the best option I came up with.

Re: Looking Forward to Postgres 19: It's About Time

#16

Post by promano »

throwaway7783 wrote: post #9
larsnystrom wrote: post #5

Why are they storing a time period (start and end date) in the first example? Why not just store the date when the price comes into effect? That would make both overlaps and time travel impossible without using any constraints.

Works when there is always an active price. Having an explicit end date allows certain rows to be inactive automatically after validity period. Think of seasonal categories/products etc which dont exist after a specific period

And even if you don't have seasonal products, you still need an end date to mark when you stop selling a product; otherwise you have to do something hacky like defining " = NULL means we stopped selling the product after " and inserting an extra record.

I think the end date should be nullable though, but valid_to is NOT NULL in the starting example... later in the article, when showing the "new way" using date ranges, it inserts a row with an open-ended range, which is more what I'd expect.

Re: Looking Forward to Postgres 19: It's About Time

#18

Post by jacques_chester »

Exciting. Honestly I expect this will do more to advance bitemporal design than decades of jawboning has.

And really, ranges are an amazing substrate for this. I've had to do this by hand in a ... less featuresome ... SQL-speaking DB and it was clunky and performed fairly unimpressively.

Re: Looking Forward to Postgres 19: It's About Time

#19

Post by advisedwang »

larsnystrom wrote: post #5

Why are they storing a time period (start and end date) in the first example? Why not just store the date when the price comes into effect? That would make both overlaps and time travel impossible without using any constraints.

JOINs and other operations become really difficult if you can't evaluate whether a row applies or not based on that row alone.

Re: Looking Forward to Postgres 19: It's About Time

#20

Post by warmwaffles »

larsnystrom wrote: post #5

Why are they storing a time period (start and end date) in the first example? Why not just store the date when the price comes into effect? That would make both overlaps and time travel impossible without using any constraints.

It's a contrived example.
Post reply on HN