On 2021-05-23 12:55:52 -0700, Bryn Llewellyn wrote:
> But it doesn't necessarily error when presented with a key that it
> doesn't have. This, too, has been discussed at length in this
> exchanges. These examples make the point.
>
> set timezone = 'UTC';
> select '2021-05-23 19:00:00 foo42bar'::timestamptz;
> select '2021-05-23 19:00:00'::timestamptz at time zone 'bar99foo';
This is an unfortunate side effect of the flexibility of posix-style
timezone specifications. That flexibility was very useful when the
format was invented in the 1980's. The Olson database may or may not
have existed at the time (Wikipedia says its "origins go back to 1986 or
earlier"), but it wasn't well known and system administrators were
expected to set a TZ environment variable with the correct rules for
their time zone, so it had to be flexible enough for all time zones in
the world (not sure if it actually achieved that goal). When the Olson
(now IANA) timezone database spread that flexibility became mostly
obsolete but it might still be needed sometimes (for example Turkey has
in recent times changed the date for a DST switch at very short notice
so some administrators may have had to override their system's rules
when the vendor didn't issue a patch in time).
> Sadly, there's no simple way to enforce a practice for applications that want
> to avoid this risk unless, maybe, every such plain timestamp expression is
> constructed programmatically.
I think there are two possibilities:
1. Give the user a list of possible timezones to choose from (maybe a
simple dropdown menu, maybe a fancy map where they can click on their
location) and don't allow them to enter timezone names manually.
2. Ignore the problem. If a user enters a time zone of "foo42bar" either
they know what they are doing or they made a mistake. The latter case is
really not that different from entering a wrong but existing timezone or
making a typo in the date or time. In any case you now have a wrong
timestamp in your database which you may or may not be able to catch via
other QA measures.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"