Timestamp discrepancy between system and user

[lazyfrosch]

There seems to be a timezone problem in timestamps within the out log.

Wrong:

• Audit log listing
• Deployment listing

Correct:

• Detail area of an audit entry
• Detail area of deployment

Container / Server environment is: UTC, date.timezone = UTC
Browser Timezone: Europe/Berlin

Your Environment

• Director version (System - About): v1.4.2
• Icinga Web 2 version and modules (System - About): 2.5.0
• Webserver, PHP versions: Apache, PHP 5.6.30

---

ref/NC/561505

[lazyfrosch]

This is a bigger problem than I though initially.

Most of timestamps in director as based on local timezone, but not that of the server, but that from the user perspective, so every change, import, sync has timestamps based on the Timezone of the user doing it.

Activity Log in addition seems to do some kind of timezone calculation.

This is something we should address soon, I guess the best way is to use all database timestamps as UTC, and translate them only in UI/CLI

@Thomas-Gelf thoughts?

[Thomas-Gelf]

That's issue #58, so a very early one. Column will be a SIGNED BIGINT with unix timestamp as milliseconds. Easy to implement, but migration queries will be expensive for legacy systems. Not a big problem, but irritating for people triggering migrations from the GUI with proxies or FPM cutting your connection after some default timeout. So, my initial idea was to defer this unless we have the possibility to run background jobs for the Icinga Web 2 frontend.

In case you want to speed this up we could add an "expensive" flag as a comment in our migration files, the GUI could then forbid running them and point you to the cli.

[lazyfrosch]

My problem is not precision, it is actually the wrong time...

A migration could be done, but most important is how we generate, store and display time.

[Thomas-Gelf]

Precision never hurts :D The fix will be the fact that it will be a unix timestamp, so no doubt about timezone.

[doctore74]

+1

…

On 8. Mar 2018, at 18:34, Thomas Gelf ***@***.***> wrote: Precision never hurts :D The fix will be the fact that it will be a unix timestamp, so no doubt about timezone. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[XnS]

Unfortunately this does not completely fix the issue.

I have no idea where it is set, but the date() function uses the Timezone of the User/Browser instead of the server timezone. Thus the times in activitylog and deployment log are not consistent across multiple time zones.

for example:

icingaweb2-module-director/library/Director/Objects/DirectorActivityLog.php

Line 103 in 14a3039

'change_time' => date('Y-m-d H:i:s'),

Server configuration:

date
date/time support 	enabled
"Olson" Timezone Database Version 	0.system
Timezone Database 	internal
Default timezone 	UTC
date.timezone	UTC	UTC

Debugging the mentioned line:

date_default_timezone_get: Europe/Paris
date: 2018-08-08 13:59:48

Tested with:

Icinga Web 2 Version: 2.6.1
Git commit b3e0b5d587c3587a09d82b3634dbe3a5dd315353

director Version: master
Git commit 14a3039f650f7978e4e3319ee5ce73851a4ce8d8

PHP Version 7.0.7

[Thomas-Gelf]

Are you sure, do you see wrong data? You're right, date() uses the timezone configured for the current web request, defaulting to your browsers time zone, eventually overridden in your personal settings. On connection setup we're telling the database this CLIENT time zone, and the database should translate timezone-aware columns correctly.

Could you please show an example we could use to reproduce erroneous data?

[XnS]

Table director_activity_log:

| 36341 | icinga_command | mongodb                      | modify      | user_berlin           | 2018-08-08 13:08:32 |
| 36342 | icinga_command | nscp-local-tasksched         | create      | user_berlin           | 2018-08-08 13:08:32 |
| 36343 | icinga_host    | testhost01                   | delete      | user_utc              | 2018-08-08 11:10:03 |
| 36344 | icinga_host    | testhost02                   | create      | user_utc              | 2018-08-08 11:12:01 |
| 36345 | icinga_host    | testhost03                   | create      | user_utc              | 2018-08-08 11:22:54 |
| 36346 | icinga_host    | timezonetest01               | create      | user_berlin           | 2018-08-08 13:27:02 |
| 36347 | icinga_host    | timezonetest01               | modify      | user_berlin           | 2018-08-08 13:27:45 |

Also the activity and deployment log in the web ui are mixed up. For example the stuff for user_berlin is on top although there are newer changes from user_utc.

[Thomas-Gelf]

@XnS: and all those changes have been made AFTER upgrading Director? I assume you're running MySQL?

[XnS]

2x yes
DB migration and redeployment was also done after upgrading.

[Thomas-Gelf]

Aaaargh... I got it. The DB column is DATETIME on MySQL and not TIMESTAMP, and therefor it doesn't carry timezone information. I'm so stupid :-/

@XnS: thanks for your help with this! Now, the problem is that a migration for this often very huge table is expensive, and with the current implementation most users run them in the UI. I do not want to risk such migrations to interrupt unexpectedly, that's why we will move that and similar tasks to a background daemon. That one however will be merged into master after v1.5.0, so I'm sorry to say that we're forced to postpone this.

It will however be followed with high priority.

[XnS]

all right, thanks for the clarification :-)

[andrewmiskell]

I saw that a fix for this was put into 1.5.0, released today. I installed this release and still seeing the issue. Do I manually need to run the migration within the database? I already applied the default schema changes via the UI once upgraded to 1.5.0.

[u238]

Hi there! any news on this?

[log1-c]

I`d also like to check if there is a new status on this. For people working from different time zones this can lead to irritations in the audit and deployment logs, when deployments are shown "in the past or future"
E.g. a deployment issued from a host in Australia is shown in the future on a host from germany:
AU:
DE: