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? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
