Please advice, weird current_timestamp issue.

[Franky Brandt]

Been using Firebird for a really long time, since the beginning really, Interbase before that, but never seen a problem like this.

We have a customer where the triggers on tables set the wrong values in their timestamp fields.

Even executing "select current_timestamp from rdb$database" in a sql returns the correct date but a time one hour before the system's current time.

The system timezone is on UTC+1 , and is correct in Windows but current_timestamps shows one hour less.

I also tried localtimestamp but that is identical, to current_timestamp one hour less.

This is a Firebird 2.5 server, Windows service.

For another application on the same server that is using a Embedded Firebird 3 i also tried getting current_timestamp and on this embedded FB3 it returns the correct values!.

The timestamps with one hour less are causing problems in the syncing of the data.

Does anyone have a idea what i need to check or change to have the correct value from current_timestamp?

Urgent advice would be greatly appreciated as i'm out of ideas

[Dimitry Sibiryakov]

Franky Brandt wrote 13.02.2024 10:10:
> Does anyone have a idea what i need to check or change to have the correct value
> from current_timestamp?

Did you try to restart Firebird server?

--
WBR, SD.

[Franky Brandt]

Yes the Firebird service has been restarted, same result.
Default we run Firebird on port 3090 but i even tried installing 2.5.9 now as second test on port 3050 but same result, current_timestamp is One hour less then system time.
Running the Firebird service with the 'log on' user credentials i am using also made no difference.

Op dinsdag 13 februari 2024 om 10:26:36 UTC+1 schreef sd:

[Virgo Pärna]

Can you post exact sample of query you are using to check timestamp?
And what is actual server timezone? And is clock correct?

Before 4 there was no timestamp with timezone, so that should not be an
issue.
For me (timezone "Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius" on
Windowsi, so currently UTC+2).
And from Firebird 2.5.9
SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE
UNION ALL
SELECT LOCALTIMESTAMP FROM RDB$DATABASE;
results
13.02.2024, 11:49:18.666
13.02.2024, 11:49:18.666
3.0.10
13.02.2024, 11:49:42.549
13.02.2024, 11:49:42.549
On the other hand Firebird 4.0.4
13.02.2024, 09:50:14.571
13.02.2024, 09:50:14.571
And result type is TIMESTAMP WITH TIMEZONE.
But
SELECT LOCALTIMESTAMP FROM RDB$DATABASE
alone gives correct result with TIMESTAMP result.

--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Franky Brandt]

Yes, both values result as
13/02/2024 10:19:47

And the system clock shows 13/2/2024 11:19

Op dinsdag 13 februari 2024 om 10:54:12 UTC+1 schreef Virgo Pärna:

[Franky Brandt]

And the weirdest of all is that the second Embedded Firebird 3 i have on that same server does show the correct current_timestamp, the Firebird 2.5.9 service does not.

Op dinsdag 13 februari 2024 om 11:21:07 UTC+1 schreef Franky Brandt:

[Dimitry Sibiryakov]

Franky Brandt wrote 13.02.2024 11:44:
> And the weirdest of all is that the second Embedded Firebird 3 i have on that
> same server does show the correct current_timestamp, the Firebird 2.5.9 service
> does not.

What if it is run as an application or Firebird 3 run as a service? Service
with "log on" user still have different environment from interactive applications.

--
WBR, SD.

[Virgo Pärna]

On 13.02.2024 12:47, 'Dimitry Sibiryakov' via firebird-support wrote:
>
>   What if it is run as an application or Firebird 3 run as a service?
> Service with "log on" user still have different environment from
> interactive applications.
>

I guess it is worth checking. But from everything I found, Windows does
not support per user timezone settings.

And neither does Firebird before version 4....

Could the server actually not be Firebird 2.5?

SELECT
rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version,
current_timestamp,
localtimestamp
from rdb$database

would allow doublechecking it by Franky.

[Franky Brandt]

I just checked Virgo

At 12:09 Windows time the query

SELECT
   rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version,
   current_timestamp,
   localtimestamp
from rdb$database

returns

Version 2.5.9

Current_timestamp 13/02/2024 11:09:27
Localtimestamp 13/02/2024 11:09:27

Op dinsdag 13 februari 2024 om 12:06:53 UTC+1 schreef Virgo Pärna:

[Mark Rotteveel]

On 13/02/2024 12:06, Virgo Pärna wrote:
> On 13.02.2024 12:47, 'Dimitry Sibiryakov' via firebird-support wrote:
>>    What if it is run as an application or Firebird 3 run as a service?
>> Service with "log on" user still have different environment from
>> interactive applications.
>
>     I guess it is worth checking. But from everything I found, Windows
> does not support per user timezone settings.

The only thing I found is that with Terminal Services, users can have
separate time zones on a single machines.

>     And neither does Firebird before version 4....

Whether Firebird supports the TIME ZONE types or not is not really
relevant for that. If the Firebird server is running on a machine
reporting 11:10 as the local time, while the user expects it is 12:10,
then the user will get the "wrong" time from their perspective, even
though the server reports the correct time according to their OS.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Virgo Pärna wrote 13.02.2024 12:06:
>     I guess it is worth checking. But from everything I found, Windows does not
> support per user timezone settings.

Per user - yes. But time zone is setting per "control set" which may vary.

--
WBR, SD.

[Virgo Pärna]

On 13.02.2024 13:13, 'Mark Rotteveel' via firebird-support wrote:
> On 13/02/2024 12:06, Virgo Pärna wrote:
>>
>>      I guess it is worth checking. But from everything I found,
>> Windows does not support per user timezone settings.
>
> The only thing I found is that with Terminal Services, users can have
> separate time zones on a single machines.
>

Ok, so there is that possibility.

>>      And neither does Firebird before version 4....
>
> Whether Firebird supports the TIME ZONE types or not is not really
> relevant for that. If the Firebird server is running on a machine
> reporting 11:10 as the local time, while the user expects it is 12:10,
> then the user will get the "wrong" time from their perspective, even
> though the server reports the correct time according to their OS.
>

What I ment is that in FirebirdSQL 4 there is DefaultTimeZone
configuration. And if it where set to UTC for example, while Windows is
set to CET, I would imagine, that localtimestamp would return different
value, than Windows clock shows. But since the server really is 2.5.9...

[Franky Brandt]

Maybe this is important but i just did a test with Firebird 2.5.9 embedded and there it shows correct.

So both tests with embedded give correct timestamps, both services i tried show time one hour less.

The IT guy of the client says it's a Windows 365 virtual machine...maybe that has something to do with it, although as has been said only one timezone can be set i think...and it's showing correctly as UTC+1.00, Brussels, Copenhagen etc

Op dinsdag 13 februari 2024 om 12:16:56 UTC+1 schreef sd:

[Virgo Pärna]

On 13.02.2024 13:23, Franky Brandt wrote:
> Maybe this is important but i just did a test with Firebird 2.5.9
> embedded and there it shows correct.
> So both tests with embedded give correct timestamps, both services i
> tried show time one hour less.
>

And services run under SYSTEM user... But how to check/set timezone for
System user...
I guess when user runs PowerShell then Get-TimeZone also reports same
timezone, as Windows GUI... Even when run as Administrator.

> The IT guy of the client says it's a Windows 365 virtual machine...maybe
> that has something to do with it, although as has been said only one
> timezone can be set i think...and it's showing correctly as UTC+1.00,
> Brussels, Copenhagen etc
>

Maybe....

[Dimitry Sibiryakov]

Franky Brandt wrote 13.02.2024 12:23:
> Maybe this is important but i just did a test with Firebird 2.5.9 embedded and
> there it shows correct.
> So both tests with embedded give correct timestamps, both services i tried show
> time one hour less.
>
> The IT guy of the client says it's a Windows 365 virtual machine...maybe that
> has something to do with it, although as has been said only one timezone can be
> set i think...and it's showing correctly as UTC+1.00, Brussels, Copenhagen etc

You would like to check values in registry keys HKEY_LOCAL_MACHINE > SYSTEM >
ControlSetXXX > Control > TimeZoneInformation and try to adjust them if they are
different.

--
WBR, SD.

[Franky Brandt]

Still looking into this.

I now made a small vcl program in Delphi that logs the current date & time and the Timezoneinformation in a logfile each 30 seconds and did the same with a small service application.

On that server oddly the delphi 'Now' function both return the date & time correct in the log files, both the visual app and the tservice but the time zone information is different, which does indicate something is different for the service (and probably also the Firebird service).

The visual app logs
Current date and time: 15/02/2024 15:32:44
bias -60  
daylightname Romance Daylight Time  
StandardName Romance Standard Time  
DTZ.TimeZoneKeyName Romance Standard Time

The service logs
Current date and time: 15/02/2024 15:32:46
bias -60  
daylightname Temps universel coordonné  
StandardName Temps universel coordonné  
DTZ.TimeZoneKeyName Romance Standard Time

So to me this is proof that on that server services run in UTC timezone...but everyone keeps telling me you can only have one timezone on the Windows server...
And i do find similar experiences on stackoverflow, like https://stackoverflow.com/questions/74277164/service-running-in-a-different-timezone-than-windows-with-a-specific-user/77999707#77999707
And there people also stated it is not possible...but the proof of the pudding is in the eating...right?

Op dinsdag 13 februari 2024 om 12:30:00 UTC+1 schreef sd:

[Dimitry Sibiryakov]

Franky Brandt wrote 15.02.2024 15:44:
> but everyone keeps telling me you can only have one timezone on the Windows
> server...

I repeat: just open registry editor on this server and look at

HKEY_LOCAL_MACHINE > SYSTEM > ControlSetXXX > Control > TimeZoneInformation

For every control set you see there.

--
WBR, SD.

[Franky Brandt]

Thanks mate, i did check that but apparently not completely, my local registry had keys that were not on that server's registry and vice versa.

I now exported that key for backup on server, exported mine, executed the .reg file on that server and presto current_timestemp now returns correct values.

Thanks a lot everyone for the assistance.

Op donderdag 15 februari 2024 om 15:49:23 UTC+1 schreef sd: