Use fbclient-interfaces to decode time-fields with time zone.

andy-123 commented 6 months ago

This PR ueses fbclient lib to decode ISC_TIME_TZ / ISC_TIME_TIMESTAMP-structs.

AFAICS isql does it the same way.

However, the following commands produces different result (flamerobin <-> isql).

set time zone 'America/Sao_Paulo'; select localtime, current_time from rdb$database;

        LOCALTIME                                   CURRENT_TIME 
        ============= ============================================== 
FR:     07:24:35.0000 08:24:35.0000 America/Sao_Paulo       
ISQL:   07:24:35.0000 07:24:35.0000 America/Sao_Paulo

TODO

[x] (1) use time-zone names from fbclient-functions to detect GMT-fallback (= failed to load ICU)
[x] (2) load fb_get_master_interface dynamic to work with older fbclient-libs (< 3.0)
~~[ ] (3) analyse "select cast('2018-01-01 16:01:19 America/Sao_Paulo' as timestamp with time zone) from rdb$database;" It returns ... 15:01 ... (fbclient-lib bug?)~~ (my icu was outdated; 2019)
[ ] (4) select current_time(stamp) returns the wrong time too. I've modified fbclient-lib to get the expected value. I'm unsure if it is a fbclient-lib bug? (common/TimeZoneUtil.cpp:TimeZoneUtil::decodeTimeStamp - line ~ 770 - i've commented "+ icuLib.ucalGet(icuCalendar, UCAL_DST_OFFSET, &icuErrorCode)" out.
~~[ ] (4a) regardless bug or not - a fix for the the "may-be-buggy"-fbclient-lib is needed.~~
[x] Test: Windows - flamerobin + older fbclient.dll (3.0/2.5) (runs?) - yes :)
[x] Test: Linux - flamerobin + older fbclient.dll (3.0/2.5) (runs?) - yes :)
[x] Test: Windows - is time-zone + time correct ? (does this PR really fix the bug?) Yes: Short story - tzdata directory was missing. Should be on the same directory as fbclient.dll.
[x] Test: Linux - is time-zone + time correct ? (does this PR really fix the bug?) Yes: Short story - self-build or old libfbclient with outdated time-zone database.

~~To point (4):~~ * isql calls setlocale(LC_CTYPE, ""); (~ line 771) .... so it's startet with no locale (GMT-TimeZone). "+ icuLib.ucalGet(icuCalendar, UCAL_DST_OFFSET, &icuErrorCode)" is 0 and has no affect.

arvanus commented 5 months ago

Hi, is there some status about the 1hour+ divergence? Thanks!

andy-123 commented 4 months ago

No, unfortunately there was no helpful answer to either your or my support request. My two options that I still see are: Either to find a open-source-tool that uses the Firebird API “correctly”, look into code of this tool, and then to be able to apply this knowledge to Flamerobin. The other option would be to use icu directly. I still have a little time this week, but I'll start pursuing these two options.

andy-123 commented 4 months ago

My implementation was already okay. The problems were caused by an incomplete / unclean fbclient installation. Long story: Under Linux the tzdata directory was (probably) not found. I was able to solve the problem by setting the environment variable LD_LIBRARY_PATH. Under Windows it was enough to copy the tzdata directory into the Flamerobin root directory. I had already copied the fbclient.dll there. @arvanus Maybe a similar problem existed in your test.

arvanus commented 4 months ago

I'll take a look in the next days, out of time this days Thanks for your great work!

andy-123 commented 2 months ago

Hi, any news about reviewing this PR?

mariuz / flamerobin

Use fbclient-interfaces to decode time-fields with time zone. #362