Dbcritic: Constructively criticizing your Postgres schema (using Idris)(channable.com)
channable.com
Dbcritic: Constructively criticizing your Postgres schema (using Idris)
https://www.channable.com/tech/dbcritic-constructively-criticizing-your-postgres-schema
15 comments
Great to see this being open sourced! Every project that relies on a Postgres database should put this in their CI pipeline, it's that good.
There is also DBLint (https://archive.codeplex.com/?p=dblint), which has a fairly comprehensive set of checks.
It is nice to see practical application of dependently typed language!
Looking through the GitHub repo it doesn't look to actually use any of the dependently typed features of Idris.
(And I see now at the end of the article that they mention this).
If this grows in the future, I'd be curious to see if the team starts to integrate any dependent types.
(And I see now at the end of the article that they mention this).
If this grows in the future, I'd be curious to see if the team starts to integrate any dependent types.
Why is this written in Idris instead of a normal language?
From the post:
dbcritic came to life as a Channable Hackathon project. dbcritic is written in Idris, a functional programming language, to get an idea of what the language and tooling are like. It is quite similar to Haskell, which we use for some of our larger projects, but it has some neat extra features, such as the early returns in do expressions, and of course dependent types (which we didn’t really use in dbcritic).
dbcritic came to life as a Channable Hackathon project. dbcritic is written in Idris, a functional programming language, to get an idea of what the language and tooling are like. It is quite similar to Haskell, which we use for some of our larger projects, but it has some neat extra features, such as the early returns in do expressions, and of course dependent types (which we didn’t really use in dbcritic).
> Because the vast majority of our use cases for storing timestamps is to represent specific, unambiguous moments in time, we pretty much always want to use the timestamptz type.
This advice is not great. One needs to specifically know their data.
Postgresql stores the timezone numerically, and not together with the location the timezone applies to. This means it is impossible to deduce DST from a timestamptz alone, but this is neccessary because two different locations in the same timezone might very possibly switch to and from DST at different times of the year.
To store an unambiguous moment in time ( e.g. when scheduling an international conference call) one can store it as a timestamptz but this doesn't gain a lot over just treating all time in the database as UTC and then converting from/to local time for input/display.
To store a time that is unambiguous in a certain location (e.g. a doctor's appointment in 4 months time) one needs to store it as timezone without timezone and an extra field with the locality. It makes no sense to store the timezone, unless you also store the DST information or unless you think you can rule out unexpected DST changes.
Storing timestamptz really makes a lot of sense for past events though - for example when logging.
So it really depends on what kind of timestamp you are trying to store, and I am not sure there is a general rule that can be applied.
This advice is not great. One needs to specifically know their data.
Postgresql stores the timezone numerically, and not together with the location the timezone applies to. This means it is impossible to deduce DST from a timestamptz alone, but this is neccessary because two different locations in the same timezone might very possibly switch to and from DST at different times of the year.
To store an unambiguous moment in time ( e.g. when scheduling an international conference call) one can store it as a timestamptz but this doesn't gain a lot over just treating all time in the database as UTC and then converting from/to local time for input/display.
To store a time that is unambiguous in a certain location (e.g. a doctor's appointment in 4 months time) one needs to store it as timezone without timezone and an extra field with the locality. It makes no sense to store the timezone, unless you also store the DST information or unless you think you can rule out unexpected DST changes.
Storing timestamptz really makes a lot of sense for past events though - for example when logging.
So it really depends on what kind of timestamp you are trying to store, and I am not sure there is a general rule that can be applied.
I often refer to this article https://phili.pe/posts/timestamps-and-time-zones-in-postgres... regarding timestamp and timestamptz
>> Because the vast majority of our use cases for storing timestamps is to represent specific, unambiguous moments in time, we pretty much always want to use the timestamptz type. > This advice is not great. One needs to specifically know their data.
I think the advice is good advice: when you want to store an unambiguous point in time, you should use the timestamptz type, as it stores the time in UTC, whatever you give it.
"To store a time that is unambiguous in a certain location (e.g. a doctor's appointment in 4 months time) one needs to store it as timezone without timezone and an extra field with the locality. It makes no sense to store the timezone, unless you also store the DST information or unless you think you can rule out unexpected DST changes."
In postgres, timestamptz doesn't store the timezone, only the UTC value, given a timezone.
I think your example of "A doctor's appointment in 4 month's time" _isn't_ an unambiguous point in time, which is why you would need to store the wall time with the timezone information.
I can save my doctor's appointment as timestamptz in 4 months time with
# SELECT timezone('Europe/London', '2021-10-07 20:00'::timestamp);
2021-10-07 19:00:00+00
However storing the Europe/London timezone doesn't help me as I can't rule out unexpected DST rule changes. If the UK parliament abolishes BST before that time, I will have to recalculate that value, otherwise I will arrive an hour early.
SELECT timezone('Europe/London', '2021-10-07 19:00Z'::timestamptz);
2021-10-07 20:00:00 (1 row)
will change to
SELECT timezone('Europe/London', '2021-10-07 19:00Z'::timestamptz);
2021-10-07 19:00:00 (1 row)
So the wall time is used, as the point in time is ambiguous.
>> Because the vast majority of our use cases for storing timestamps is to represent specific, unambiguous moments in time, we pretty much always want to use the timestamptz type. > This advice is not great. One needs to specifically know their data.
I think the advice is good advice: when you want to store an unambiguous point in time, you should use the timestamptz type, as it stores the time in UTC, whatever you give it.
"To store a time that is unambiguous in a certain location (e.g. a doctor's appointment in 4 months time) one needs to store it as timezone without timezone and an extra field with the locality. It makes no sense to store the timezone, unless you also store the DST information or unless you think you can rule out unexpected DST changes."
In postgres, timestamptz doesn't store the timezone, only the UTC value, given a timezone.
I think your example of "A doctor's appointment in 4 month's time" _isn't_ an unambiguous point in time, which is why you would need to store the wall time with the timezone information.
I can save my doctor's appointment as timestamptz in 4 months time with
# SELECT timezone('Europe/London', '2021-10-07 20:00'::timestamp);
2021-10-07 19:00:00+00
However storing the Europe/London timezone doesn't help me as I can't rule out unexpected DST rule changes. If the UK parliament abolishes BST before that time, I will have to recalculate that value, otherwise I will arrive an hour early.
SELECT timezone('Europe/London', '2021-10-07 19:00Z'::timestamptz);
2021-10-07 20:00:00 (1 row)
will change to
SELECT timezone('Europe/London', '2021-10-07 19:00Z'::timestamptz);
2021-10-07 19:00:00 (1 row)
So the wall time is used, as the point in time is ambiguous.
Hopefully if the UK Parliament abolished BST, postgres maintainers would update how the "Europe/London" timezone works to account for this change (as well as every other library that manages timezones). I would also expect there to be a transition period to ensure everyone is brought to speed and the relevant updates are made
Certainly, in my example above I would have to recalculate the UTC value using the new Europe/London timezone to ensure the correct time is used.
The point being, the wall time is an ambiguous time: its point on the timeline can change based on the legislator's pen.
The point being, the wall time is an ambiguous time: its point on the timeline can change based on the legislator's pen.
well unless the tzdb does not drop dst values past a certain time, it would always work if non utc values are used.
> To store an unambiguous moment in time ( e.g. when scheduling an international conference call) one can store it as a timestamptz but this doesn't gain a lot over just treating all time in the database as UTC and then converting from/to local time for input/display.
This "interpret tz-less time as utc" in requires discipline to treat it counter to the default human interpretation of the data type, so is error prone. DB client libs will default to using ambiguous time types like LocalDate etc. Wouldn't recommend it.
This "interpret tz-less time as utc" in requires discipline to treat it counter to the default human interpretation of the data type, so is error prone. DB client libs will default to using ambiguous time types like LocalDate etc. Wouldn't recommend it.
Yeah, another way to think about it is whether you're:
* Storing info about an instant in time when something happened (or is going to happen), which would be the same across the entire universe (relativity notwithstanding)
* You're storing info about when something should happen (or something happen) in the context of a specific location
Typically, if you're recording things that happened (think logging an event), then you want to store the instant that they happened. This means storing them in UTC and translating to the user's time zone on display. If you're storing info about when something should happen (think of an appointment or meeting) then you want to store it in the context of a single, specific location, and translate to other user's time zones on demand.
Scheduling for future events should always be in the context of a specific time zone or location. If someone from another time zone needs to see the translation into their time then you apply the what the differential will be between the two time zones at the time the event is supposed to occur in the source time zone (so if the destination time zone will be +6 at the date/time of the event in the source tz then you add 6 hours to the source date/time).
It's complicated doing it like that but it's the only way to account for all the various ways TZs can change that you hadn't accounted for. The key is understanding what you're measuring date/time relative to.
* Storing info about an instant in time when something happened (or is going to happen), which would be the same across the entire universe (relativity notwithstanding)
* You're storing info about when something should happen (or something happen) in the context of a specific location
Typically, if you're recording things that happened (think logging an event), then you want to store the instant that they happened. This means storing them in UTC and translating to the user's time zone on display. If you're storing info about when something should happen (think of an appointment or meeting) then you want to store it in the context of a single, specific location, and translate to other user's time zones on demand.
Scheduling for future events should always be in the context of a specific time zone or location. If someone from another time zone needs to see the translation into their time then you apply the what the differential will be between the two time zones at the time the event is supposed to occur in the source time zone (so if the destination time zone will be +6 at the date/time of the event in the source tz then you add 6 hours to the source date/time).
It's complicated doing it like that but it's the only way to account for all the various ways TZs can change that you hadn't accounted for. The key is understanding what you're measuring date/time relative to.