Looks like Flamerobin is subtracting the timezone again or something like that.

[arvanus]

--set time zone 'America/Sao_paulo';
--set bind of time with time zone to legacy;
--set bind of timestamp with time zone to legacy;
select localtime, current_time, CURRENT_TIMESTAMP from rdb$database

Both columns should have the same timestamp value for Fb4+

[arvanus]

See more here:
FirebirdSQL/firebird#7806 (comment)

[arvanus]

Note: Flamerobin is NOT subtracting the TZ

[mrotteveel]

As I also commented on the issue on the Firebird tracker, it looks like FlameRobin is showing the UTC time.

[arvanus]

@andy-123 can you take a look?
Looks like we need to load ICU someway, and convert every single field to the specific offset taking account the timezone and time itself
I don't know if we can do it using directly fbclient.dll or just by using ICU dll
For now I'm thinking to change Flamerobin behavior to always show time as GMT+0:00 to avoid misinterpretation of the value

[andy-123]

I'm sorry for being late. I hope to find time for this in the next few weeks.

[arvanus]

Note: probably the correct place to change it could be at IBPP source, not sure

[andy-123]

See PR (Draft) #362 for current state.

[andy-123]

@arvanus
The PR #362 is almost finished. Unfortunately, the time displayed (localtime/current_time) in the time zone "America/Sao_Paulo" does not match what isql shows. (See also the description in the PR).

It's unclear to me where the BUG comes from.
When debugging isql, I noticed that the DST (Daylight save time) for America/Sao_Paulo is added here. This is responsible for the one hour difference. Sao_Paulo abolished the DST in 2019. This indicates an outdated ICU version. However, my linux and also fbclient/isql seem to have a current version of ICU.
And why is isql correct?
I currently have no idea how to solve this problem. Maybe someone here can help and/or give a good hint or something.

[arvanus]

Hi, I'll take a look what it could be ASAP
Thanks

[andy-123]

Thank you.
Maybe this is still helpful:
It depends on the DST (Daylight saving time). With PR #362 applied, the time is displayed correctly if you subtract or add 1/2 year.

[arvanus]

SO, I tried this SQL in a "legacy" Firebird 4.0

select  localtime, localtimestamp, current_time, CURRENT_TIMESTAMP, 
        cast(localtime as varchar(42)) as cast_localtime, 
        cast(localtimestamp as varchar(42)) as cast_localtimestamp, 
        cast(current_time as varchar(42)) as cast_current_time, 
        cast(CURRENT_TIMESTAMP as varchar(42)) as cast_CURRENT_TIMESTAMP
      from rdb$database

And is showing the same time correctly

BUT in another server I get the time with +1Hour, very strange :

(also in isql)

When I try in another server with timezone (America/Sao_paulo) manually defined in the conf , I get another behaviour

Note: I'm in a hurry now, so maybe I missed something

[andy-123]

Thanks for testing. Very strange, ...
Is isql embedded or connected via network? I suspect that this makes a difference.
In the first isql query, the time zone is displayed as "-3:00". This could be a problem loading the ICU library.

[arvanus]

Via network, remote servers.
Also noted that if the server is using the default OS timezone, or is using a fixed in firebird.conf equal to the OS timezone I get different results somehow

[arvanus]

@andy-123 I found some strange behavior related with manually setting DefaultTimeZone = America/Sao_paulo
When not defined the output is correct, but the TZ is odd

I submitted a post at firebird-support
https://groups.google.com/g/firebird-support/c/5FA5R8Pjvog/m/EBqlQ-AwAwAJ

SQL:

select  localtime, current_time, 
        cast(localtime as varchar(42)) as cast_localtime, 
        cast(current_time as varchar(42)) as cast_current_time
    from rdb$database

with DefaultTimeZone =

with DefaultTimeZone = America/Sao_paulo

[andy-123]

I created a small Lazarus project that has the same time problem as flamerobin. I have created a support request for this.
https://groups.google.com/g/firebird-support/c/vic0_wwpOVw