CURRENT_TIMESTAMP returns a diferent time than CURRENT_TIME...

[adrianlmm]

I'm testing 5.0.0 RC1 x64 in Windows 11 and CURRENT_TIMESTAMP returns a diferent value than CURRENT_TIME.

set bind of time with time zone to legacy; select CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME from RDB$DATABASE;

show this:

CURRENT_TIME CURRENT_TIMESTAMP LOCALTIME 09:06:48.000 16.10.2023, 16:06:48.620 09:06:48.000

As you can see CURRENT_TIMESTAMP gives the time part wrong.

[asfernandes]

What's your time zone (show full result of CURRENT_TIMESTAMP)?

[adrianlmm]

In this attached image you can see the problem.

[asfernandes]

Please look if there is any related problem in firebird.log. And run the same commands in ISQL and send the result.

[AlexPeshkoff]

On 10/17/23 06:47, adrianlmm wrote:

In this attached image you can see the problem. firebird_time_zone https://user-images.githubusercontent.com/17146064/275705520-968e64a7-cc56-4808-b1e4-0df69bfa2713.png

— Reply to this email directly, view it on GitHub https://github.com/FirebirdSQL/firebird/issues/7806#issuecomment-1765617943, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA44OUJPNQVTTKTINXVTH2TX7X5VZAVCNFSM6AAAAAA6CONZF2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONRVGYYTOOJUGM. You are receiving this because you are subscribed to this thread.Message ID: @.***>

This is effect of missing icu on client, almost similar to:

SQL> select CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME from RDB$DATABASE;

CURRENT_TIME                    12:43:22.0000 CURRENT_TIMESTAMP               2023-10-17 09:43:22.2350 GMT* LOCALTIME                       12:43:22.0000

I.e. datatypes 'with timezone' displayed in GMT. What I do not understand why was GMT* lost.

[aafemt]

Because author of the ticket used binding to legacy only for time but not timestamp and his tool is unable to handle TZ types at all displaying only GMT part of them.

[AlexPeshkoff]

Yes, certainly. But why text 'GMT*' is missing in the end? What configuration was used to not display timezone name at all?

[mrotteveel]

Yes, certainly. But why text 'GMT*' is missing in the end? What configuration was used to not display timezone name at all?

Because they used set bind of time with time zone to legacy;, which means CURRENT_TIME is converted to TIME WITHOUT TIME ZONE and CURRENT_TIMESTAMP is converted to TIMESTAMP WITHOUT TIME ZONE, and GMT* is ISQL-side rendering for WITH TIME ZONE types if ICU is missing.

[mrotteveel]

Also, the tool used is FlameRobin, not ISQL, so it will not render GMT* (assuming FlameRobin even supports with time zone types, not actually sure about that).

[mrotteveel]

@adrianlmm is it also reproducable in ISQL? Maybe this problem is specific to FlameRobin, and not Firebird itself.

[aafemt]

which means CURRENT_TIME is converted to TIME WITHOUT TIME ZONE and CURRENT_TIMESTAMP is converted to TIMESTAMP WITHOUT TIME ZONE

Are you sure that mapping for TIME also includes TIMESTAMP?

[arvanus]

Looks like something related to Flamerobin America/Sao_Paulo is -03:00. Looks like Flamerobin is NOT subtracting the timezone again or something like that.

[mrotteveel]

which means CURRENT_TIME is converted to TIME WITHOUT TIME ZONE and CURRENT_TIMESTAMP is converted to TIMESTAMP WITHOUT TIME ZONE

Are you sure that mapping for TIME also includes TIMESTAMP?

You're right, and that explains the difference between the values.

[mrotteveel]

It looks rather like FlameRobin is displaying the UTC-time for WITH TIME ZONE types.

[arvanus]

Any Firebird dev know if I can get the related timezone offset directly from the XSQLVAR (and how to retrieve this?)

https://github.com/mariuz/flamerobin/blob/e81c26cc9f21484a8c3f53ee72444abc9edea989/src/ibpp/row.cpp#L1462-L1463

Today Flamerobin extracts from sqldata an ISC_TIMESTAMP_TZ with time/timestamp and the timezone as an ISC_SHORT. I'm seeing here that Fr has declared an ISC_TIMESTAMP_TZ_EX but I tried to use and the ext_offset comes zeroed

[mrotteveel]

ISC_TIMESTAMP_TZ_EX only applies when you bind to EXTENDED with SET BIND.

[mrotteveel]

For a normal TIMESTAMP WITH TIME ZONE, it encodes either an offset or the time zone id, with the extended variant, it will encode the offset or time zone id followed by the offset (in the case of an offset value, this means it encodes the offset twice).

[mrotteveel]

That said, I think this is a question which should be asked on firebird-devel.

[asfernandes]

ISC_TIMESTAMP_TZ_EX only applies when you bind to EXTENDED with SET BIND.

Actually, types are used as the code accessing the Firebird library wants. This is the authority, not SET BIND.

If ISC_TIMESTAMP_TZ_EX is easier for an access component, so use it (assuming the library is written knowing its existence). Just change the type after describe.

[mrotteveel]

Actually, types are used as the code accessing the Firebird library wants. This is the authority, not SET BIND.

But you won't have the "extended" information in it, so no offset.

[AlexPeshkoff]

On 10/18/23 10:52, Mark Rotteveel wrote:

Actually, types are used as the code accessing the Firebird
library wants. This is the authority, not |SET BIND|.

But you won't have the "extended" information in it, so no offset.

Mark, normally offset is calculated by client using ICU by TZ code. Extended is needed when people do not have ICU on client and have absolutely no desire/ability to install it there. The exact sign of such behavior (missing ICU) is TZ named 'GMT*'. I've already lost myseif in this thread - how does isql work on problematic system?

[mrotteveel]

Mark, normally offset is calculated by client using ICU by TZ code. Extended is needed when people do not have ICU on client and have absolutely no desire/ability to install it there. The exact sign of such behavior (missing ICU) is TZ named 'GMT*'. I've already lost myseif in this thread - how does isql work on problematic system?

The OP is not using ISQL, they are using FlameRobin.

[AlexPeshkoff]

@adrianlmm Can you try with isql - what timestamp text will you get?

[mrotteveel]

@AlexPeshkoff We've already established this is a display problem in FlameRobin (it is showing the UTC time for WITH TIME ZONE types), not a Firebird problem. There is no need for further analysis on our side.

[AlexPeshkoff]

On 10/18/23 11:53, Mark Rotteveel wrote:

We've already established this is a display problem in FlameRobin (it is showing the UTC time for WITH TIME ZONE types), not a Firebird problem. There is no need for further analysis on our side.

Sorry - I've said that I'm a bit lost in this thread :-)

[asfernandes]

Actually, types are used as the code accessing the Firebird library wants. This is the authority, not SET BIND.

But you won't have the "extended" information in it, so no offset.

Of course it will. SET BIND is just about what describe API will do. Definitive type is what is specified after it and may be changed in code.

[mrotteveel]

Of course it will. SET BIND is just about what describe API will do. Definitive type is what is specified after it and may be changed in code.

Not if you only use the struct ISC_TIMESTAMP_TZ_EX, you will also need to request the type SQL_TIMESTAMP_TZ_EX in the SQLDA (to be clear, I haven't checked what FlameRobin is actually doing).

[adrianlmm]

mrotteveel

here it is.