On 18/05/2020 04:00, Alex Peshkoff via Firebird-devel wrote: > On 2020-05-16 05:22, Adriano dos Santos Fernandes wrote: >> On 15/05/2020 12:46, Mark Rotteveel wrote: >>> The decision to use 2020-01-01 as date for some of the time with time >>> zone conversion leads to, in my opinion, confusing behaviour: >>> >>> select >>> time'13:25:32.1235 Europe/Amsterdam' at time zone 'utc' as t1, >>> cast(time'13:25:32.1235 Europe/Amsterdam' as timestamp with time >>> zone) >>> at time zone 'utc' as t2, >>> cast(cast(time'13:25:32.1235 Europe/Amsterdam' as timestamp with >>> time >>> zone) as time with time zone) at time zone 'utc' as t3, >>> cast(cast(time'13:25:32.1235 Europe/Amsterdam' as timestamp with >>> time >>> zone) at time zone 'utc' as time with time zone) as t4 >>> from rdb$database; >>> >>> yields: >>> >>> T1 12:25:32.1235 UTC >>> T2 2020-05-15 11:25:32.1235 UTC >>> T3 12:25:32.1235 UTC >>> T4 11:25:32.1235 UTC >>> >>> The previous behaviour where the current date was used (in line with >>> the >>> SQL-standard requirements for using the current date when casting from >>> TIME WITH TIME ZONE to TIME WITH TIMESTAMP) would make all these >>> consistent (at 11:25:32.1235 UTC). >>> >> First, the previous behavior, as you know, is not viable: it broke >> indexes, foreign keys, unique constraints, stored data. >> >> The current behavior is not non-standard, as standard has only >> offset-based time zones, and with offsets the current behavior is >> identical to the previous one. >> >> Do you have an alternative (that do not broke things), better than use >> fixed date (as Oracle does with 0001-01-01 and it even more broke >> behavior) or a recent date? > > Telling true I have bug desire to answer that error 'Invalid > operation' should be raised because operation is really invalid, it > has undefined result (something near to zerodivide). But I'm afraid > users do not like such suggestion... > If you talk only about conversion between region-based times/tz and timestamps or time without time zone, maybe...
About usage of region-based times/tz and comparison with others region-based times/tz, I think the current behavior is ok. It's not perfect, but not very problematic. It's better to allow people to use region-based times/tz (to say things as "that event happens in that time of given country every day") than do not. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
