3 ms·

> Your database will not fix your bad data when that happens. Because it’s not bad data, HaHaOnlySerious. I tell developers that if the database attribute acce

by no-s 5y ago

> Your database will not fix your bad data when that happens. Because it’s not bad data, HaHaOnlySerious. I tell developers that if the database attribute accepts a value it must be in the domain of allowable values. Often people refuse check constraints in order to avoid expressing business logic in the database.

GormanFletcher 5y ago
The GP's example doesn't involve invalid datetimes. The datetimes aren't out of bounds or invalid in any way that a check constraint would detect. They've just become factually incorrect ("bad data"), because they are derived data that wasn't updated when the derivation rules changed (i.e., regulatory changes). If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. Storing the time zone abbreviation (e.g., EST) is inadvisable, as computers sometimes care whether you asked for EST vs EDT. Storing the time offset (e.g., -500) is incorrect, as it has the same pitfalls as storing UTC - you're precomputing the locale's expected time offset at storage time, and your data won't automatically be corrected if time regulations change. If you're storing historical timestamps, UTC is fine because you can safely convert it to whatever time zone you want to display, knowing that changes to time zone / DST regulations tend not to affect the past.
- no-s 5y ago
  > If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. At this point in the process first normal form flies out the window. Trying to generalize too much can lead you down some weird garden paths. If it looks like you need a function to validate an prospective column value then you probably need to model the value as a relation corresponding to the function parameters. Then you can make it into a foreign key constraint and get on with your project. I truly appreciate the efforts of those who attempt to expand the utility of datetime value representation to perfect a wider variety of denotational semantics. But with a relational model it may be better to delegate to simpler abstraction sufficient to the specific case.

by no-s 5y ago

GormanFletcher 5y ago

The GP's example doesn't involve invalid datetimes. The datetimes aren't out of bounds or invalid in any way that a check constraint would detect. They've just become factually incorrect ("bad data"), because they are derived data that wasn't updated when the derivation rules changed (i.e., regulatory changes). If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. Storing the time zone abbreviation (e.g., EST) is inadvisable, as computers sometimes care whether you asked for EST vs EDT. Storing the time offset (e.g., -500) is incorrect, as it has the same pitfalls as storing UTC - you're precomputing the locale's expected time offset at storage time, and your data won't automatically be corrected if time regulations change. If you're storing historical timestamps, UTC is fine because you can safely convert it to whatever time zone you want to display, knowing that changes to time zone / DST regulations tend not to affect the past.

no-s 5y ago
> If you're storing future datetimes that semantically represent wall clock time, you need to store the locale time plus the full time zone (such as America/New_York) so that your program does the right thing in response to any common regulatory changes that happen after you store the value. At this point in the process first normal form flies out the window. Trying to generalize too much can lead you down some weird garden paths. If it looks like you need a function to validate an prospective column value then you probably need to model the value as a relation corresponding to the function parameters. Then you can make it into a foreign key constraint and get on with your project. I truly appreciate the efforts of those who attempt to expand the utility of datetime value representation to perfect a wider variety of denotational semantics. But with a relational model it may be better to delegate to simpler abstraction sufficient to the specific case.