Importing local date/time data without a timezone or place (I've seen lots of financial records like this). You can no longer answer questions like, did this happen in broad daylight or in the dark? You can no longer recover the local time of the event. Converting to UTC and throwing away the timezone means you're losing information. Importing event/action data that contains date/time values with a timezone but insufficient information on the place where the event occurred. I agree that this is the preferred way of dealing with it. (Of course, similar issues can happen due to user error if two applications have different time zone settings and the user mistakenly enter a timestamp in the wrong TZ, but that's definitely not solved by making time zones a part of the data itself) If all applications and databases only concern themselves with unix timestamps, and the conversion to a specific timezone only happens in the application layer upon display, this type of issue simply does not happen, because time "1638097466" when I'm writing this is the exact same time everywhere on the globe. This is exactly the type of issue that happens because you involve timezones in your source data. > You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Timezones is fundamentally a data presentation concern, and I strongly believe they should not be a part of the source data. Thirdly, what is the value of tracking "source timezone"? This is solving a non-existent problem: if you are getting the timestamp in unix epoch from the source, and you're storing it in unix epoch, the "source timezone" is already known: it's UTC just like all unix timestamps are. Secondly, if you're in "SQLite Strict Land" and you don't have access to abstractions like "tell the database this is a date", then the best way of storing timestamps is unix epoch, I would be extremely surprised if databases don't already do this behind the scenes when abstracting away things like dates for the user. I think it is the kind of feature likely to appeal to them, it should be relatively simple to implement (especially if they don't implement ALTER DOMAIN, only CREATE and DROP), but provide a lot of power in exchange.įirst of all, I don't see how using unix epoch timestamps can be called "premature optimization", it's a pretty widely used and standardized way of saving timestamps. The SQLite developers haven't said they plan to do this, but I imagine they are thinking about it and may do it at some point. I think Postgres is the only major relational database to implement CREATE DOMAIN, although there are some more obscure RDBMSes which support it too (such as Firebird, Interbase, Sybase/SAP SQL Anywhere).
Examples:ĬREATE DOMAIN UUID AS TEXT CHECK (value REGEXP '') ĬREATE DOMAIN UUID AS BINARY CHECK (LENGTH(value) = 16)
What makes it really powerful is you can also define a CHECK constraint, NOT NULL constraint, and DEFAULTs to be automatically applied to table columns of this custom type. If they did implement CREATE DOMAIN I expect it would be supported for STRICT tables only. They could use something like "unknown types are equivalent to ANY" but that would mean they couldn't implement CREATE DOMAIN in the future without breaking backward compatibility. Without CREATE DOMAIN, for STRICT tables they don't know which basic type to use for an unknown type name. That allows you to define a custom type alias, for example: I think a logical future step would be to add the SQL-99 CREATE DOMAIN statement.
0 kommentar(er)
