Timestamp are not correct

heynst · June 19, 2024, 1:36pm

Infos:

Used Zammad version: 6.1
Used Zammad installation type: package
Operating system: debian 12
Browser + version: any

Expected behavior:

The local time should accurately reflect the current time for the specified timezone.

Actual behavior:

The local time in the whole system is incorrect, showing two hours in the past from the true local time. In the postgre db is also the wrong timestamp.

Steps to reproduce the behavior:

I Installed a fresh zammad 6.1 in debian 12 and restore it from an zammad 6.1 from debian 10

System Time Settings:

root@server:~# timedatectl
               Local time: Wed 2024-06-19 14:29:08 CEST
           Universal time: Wed 2024-06-19 12:29:08 UTC
                 RTC time: Wed 2024-06-19 12:30:49
                Time zone: Europe/Berlin (CEST, +0200)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no

Ticket creation at 2024-06-19 14:58:13.431759+02

Data in postgres

zammad=# select id, title, created_at from tickets where id = 45356;
  id   |         title          |          created_at
-------+------------------------+-------------------------------
 45356 | Test Ticket at - 14:58 | 2024-06-19 **12:58:13.431759+02**

Zammad Rails Console

Loading production environment (Rails 6.1.7.6)
irb(main):001:0> Time.zone
=> #<ActiveSupport::TimeZone:0x00007fddfeacbbb8 @name="UTC", @tzinfo=#<TZInfo::DataTimezone: Etc/UTC>, @utc_offset=nil>
irb(main):002:0> Time.zone.now
=> Wed, 19 Jun 2024 13:00:58.289481063 UTC +00:00
irb(main):003:0> Ticket.find(45356)
=>
#<Ticket:0x00007fddfc8f3e00
 id: 45356,
 group_id: 3,
 priority_id: 2,
 state_id: 2,
 organization_id: 3,
 number: "1745104",
 title: "Test Ticket at - 14:58",
 owner_id: 1,
 customer_id: 17187,
 note: nil,
 first_response_at: nil,
 first_response_escalation_at: nil,
 first_response_in_min: nil,
 first_response_diff_in_min: nil,
 close_at: nil,
 close_escalation_at: nil,
 close_in_min: nil,
 close_diff_in_min: nil,
 update_escalation_at: nil,
 update_in_min: nil,
 update_diff_in_min: nil,
 last_contact_at: Wed, 19 Jun 2024 10:58:13.527382000 UTC +00:00,
 last_contact_agent_at: nil,
 last_contact_customer_at: Wed, 19 Jun 2024 10:58:13.527382000 UTC +00:00,
 last_owner_update_at: nil,
 create_article_type_id: 5,
 create_article_sender_id: 2,
 article_count: 1,
 escalation_at: nil,
 pending_time: nil,
 type: nil,
 time_unit: nil,
 preferences: {},
 updated_by_id: 17187,
 created_by_id: 17187,
 created_at: Wed, 19 Jun 2024 10:58:13.431759000 UTC +00:00,
 updated_at: Wed, 19 Jun 2024 10:58:13.648995000 UTC +00:00,
 marke: "",
 grund: "",
 kundennummer: "",
 artikel: "",
 artikelid: nil,
 customer_type: "",
 serialno: "",
 kostenaufwand: "",
 servicelevel: "",
 kostenbetrag: "",
 versand: "",
 last_close_at: nil>
irb(main):004:0>

MrGeneration · June 19, 2024, 1:38pm

Zammad stores dates in UTC.
If you’re not providing Zammad with the correct timezone (see branding) or your user account has a different time zone, then what you’ll see will differ.

Branding — Zammad Admin Documentation documentation

6.1 is a highly outdated Zammad version and has security advisories. Upgrade asap.

heynst · June 19, 2024, 1:52pm

Yes sorry the branding is set to Europe/Berlin (GMT+2). But what do you mean with user account has a different time zone?
I need to do this in zammad 6.1 because this is a test system on debian 12 and I had to migrate from zammad 3.5 from debian 10 an the higest zammad version on debian 10 is 6.1 after a migration i can upgrade to 6.3.

*In zammad on debian 10 with zammad 6.1 every thing works correct.
Is there a problem with debian 12 and zammad 6.1. I think it is stored wrong in the database because zammad calculated an utc time and has added the cest (+02)

MrGeneration · June 19, 2024, 2:33pm

Well then I am sorry, I won’t help.
We’re supporting the current stable version of Zammad. Everything else if your problem I am afraid.

dvanzuijlekom · June 19, 2024, 3:45pm

Could you do a \d+ tickets and check what datatype your created_at column is in the database? On my Zammad 6.3 test instance, it is timestamp(3) without time zone and I’m curious where your timezone offset in your database is coming from, as it shouldn’t be there.

I reproduced your steps and created a ticket via the webinterface, at around 17:13 (my local timezone, Europe/Amsterdam). It is stored in the database as a UTC timestamp, without the offset:

The Rails console displays the same correct time and timezone for the stored timestamp:

heynst · June 20, 2024, 7:10am

Thank you very much. I do the “\d+ tickets” command, and I have timestamp with time zone.

   Table "public.tickets"
            Column            |           Type           | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description
------------------------------+--------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
 id                           | bigint                   |           | not null | nextval('tickets_id_seq'::regclass) | plain    |             |              |
 group_id                     | bigint                   |           | not null |                                     | plain    |             |              |
 priority_id                  | bigint                   |           | not null |                                     | plain    |             |              |
 state_id                     | bigint                   |           | not null |                                     | plain    |             |              |
 organization_id              | bigint                   |           |          |                                     | plain    |             |              |
 number                       | character varying(60)    |           | not null |                                     | extended |             |              |
 title                        | character varying(250)   |           | not null |                                     | extended |             |              |
 owner_id                     | bigint                   |           | not null |                                     | plain    |             |              |
 customer_id                  | bigint                   |           | not null |                                     | plain    |             |              |
 note                         | character varying(250)   |           |          | NULL::character varying             | extended |             |              |
 first_response_at            | timestamp with time zone |           |          |                                     | plain    |             |              |
 first_response_escalation_at | timestamp with time zone |           |          |                                     | plain    |             |              |
 first_response_in_min        | bigint                   |           |          |                                     | plain    |             |              |
 first_response_diff_in_min   | bigint                   |           |          |                                     | plain    |             |              |
 close_at                     | timestamp with time zone |           |          |                                     | plain    |             |              |
 close_escalation_at          | timestamp with time zone |           |          |                                     | plain    |             |              |
 close_in_min                 | bigint                   |           |          |                                     | plain    |             |              |
 close_diff_in_min            | bigint                   |           |          |                                     | plain    |             |              |
 update_escalation_at         | timestamp with time zone |           |          |                                     | plain    |             |              |
 update_in_min                | bigint                   |           |          |                                     | plain    |             |              |
 update_diff_in_min           | bigint                   |           |          |                                     | plain    |             |              |
 last_contact_at              | timestamp with time zone |           |          |                                     | plain    |             |              |
 last_contact_agent_at        | timestamp with time zone |           |          |                                     | plain    |             |              |
 last_contact_customer_at     | timestamp with time zone |           |          |                                     | plain    |             |              |
 last_owner_update_at         | timestamp with time zone |           |          |                                     | plain    |             |              |
 create_article_type_id       | bigint                   |           |          |                                     | plain    |             |              |
 create_article_sender_id     | bigint                   |           |          |                                     | plain    |             |              |
 article_count                | bigint                   |           |          |                                     | plain    |             |              |
 escalation_at                | timestamp with time zone |           |          |                                     | plain    |             |              |
 pending_time                 | timestamp with time zone |           |          |                                     | plain    |             |              |
 type                         | character varying(100)   |           |          | NULL::character varying             | extended |             |              |
 time_unit                    | numeric(6,2)             |           |          | NULL::numeric                       | main     |             |              |
 preferences                  | text                     |           |          |                                     | extended |             |              |
 updated_by_id                | bigint                   |           | not null |                                     | plain    |             |              |
 created_by_id                | bigint                   |           | not null |                                     | plain    |             |              |
 created_at                   | timestamp with time zone |           | not null |                                     | plain    |             |              |
 updated_at                   | timestamp with time zone |           | not null |                                     | plain    |             |              |
 marke                        | character varying(255)   |           |          | NULL::character varying             | extended |             |              |
 grund                        | character varying(255)   |           |          | NULL::character varying             | extended |             |              |
 kundennummer                 | character varying(120)   |           |          | NULL::character varying             | extended |             |              |
 artikel                      | character varying(3000)  |           |          | NULL::character varying             | extended |             |              |
 artikelid                    | bigint                   |           |          |                                     | plain    |             |              |
 customer_type                | character varying(255)   |           |          | NULL::character varying             | extended |             |              |
 serialno                     | character varying(1000)  |           |          | NULL::character varying             | extended |             |              |
 kostenaufwand                | character varying(255)   |           |          | NULL::character varying             | extended |             |              |
 servicelevel                 | character varying(255)   |           |          | NULL::character varying             | extended |             |              |
 kostenbetrag                 | character varying(120)   |           |          | NULL::character varying             | extended |             |              |
 versand                      | character varying(255)   |           |          | NULL::character varying             | extended |             |              |
 last_close_at                | timestamp with time zone |           |          |                                     | plain    |             |              |
Indexes:
    "idx_17249_primary" PRIMARY KEY, btree (id)
    "idx_17249_fk_rails_b62b455ecb" btree (organization_id)
    "idx_17249_fk_rails_edf0f77848" btree (updated_by_id)
    "idx_17249_index_tickets_on_close_at" btree (close_at)
    "idx_17249_index_tickets_on_close_diff_in_min" btree (close_diff_in_min)
    "idx_17249_index_tickets_on_close_escalation_at" btree (close_escalation_at)
    "idx_17249_index_tickets_on_close_in_min" btree (close_in_min)
    "idx_17249_index_tickets_on_create_article_sender_id" btree (create_article_sender_id)
    "idx_17249_index_tickets_on_create_article_type_id" btree (create_article_type_id)
    "idx_17249_index_tickets_on_created_at" btree (created_at)
    "idx_17249_index_tickets_on_created_by_id" btree (created_by_id)
    "idx_17249_index_tickets_on_customer_id" btree (customer_id)
    "idx_17249_index_tickets_on_customer_id_and_state_id_and_created" btree (customer_id, state_id, created_at)
    "idx_17249_index_tickets_on_escalation_at" btree (escalation_at)
    "idx_17249_index_tickets_on_first_response_at" btree (first_response_at)
    "idx_17249_index_tickets_on_first_response_diff_in_min" btree (first_response_diff_in_min)
    "idx_17249_index_tickets_on_first_response_escalation_at" btree (first_response_escalation_at)
    "idx_17249_index_tickets_on_first_response_in_min" btree (first_response_in_min)
    "idx_17249_index_tickets_on_group_id" btree (group_id)
    "idx_17249_index_tickets_on_group_id_and_state_id" btree (group_id, state_id)
    "idx_17249_index_tickets_on_group_id_and_state_id_and_close_at" btree (group_id, state_id, close_at)
    "idx_17249_index_tickets_on_group_id_and_state_id_and_created_at" btree (group_id, state_id, created_at)
    "idx_17249_index_tickets_on_group_id_and_state_id_and_owner_id" btree (group_id, state_id, owner_id)
    "idx_17249_index_tickets_on_group_id_and_state_id_and_updated_at" btree (group_id, state_id, updated_at)
    "idx_17249_index_tickets_on_group_id_state_id_owner_id_close_at" btree (group_id, state_id, owner_id, close_at)
    "idx_17249_index_tickets_on_group_id_state_id_owner_id_created_a" btree (group_id, state_id, owner_id, created_at)
    "idx_17249_index_tickets_on_group_id_state_id_owner_id_updated_a" btree (group_id, state_id, owner_id, updated_at)

What is the correct way to fix this problem in your opinion

heynst · June 20, 2024, 7:17am

My postgresql version is

root@server:~# apt-get -s install postgresql
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
postgresql is already the newest version (15+248).
postgresql set to manually installed.
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

psql (15.6 (Debian 15.6-0+deb12u1))

Should I install an older version, or is there a default type of timestamps which can be modified?

dvanzuijlekom · June 20, 2024, 8:18am

As I thought, that’s probably where your incorrect timestamp handling is coming from.

I don’t know. This is something which should be handled by Zammad, with database changes/migrations during installs/upgrades. It might be solved automatically when performing the upgrade path to 6.3. I don’t know the history of the datatypes of these columns and if/when/why they were changed at some point.

No, you shouldn’t need to touch your database version in order to be able to fix this.
Also, you probably shouldn’t touch the column by yourself, as this might lead to even more unexpected behaviour in Zammad. I would suggest what MrGeneration already said: to upgrade to 6.3 and see if the issue is still present. If it is, you might need to manually perform a data migration and DDL change.

heynst · June 20, 2024, 9:27am

Tank you very much i will check some things and give you an update when I’m done

heynst · July 2, 2024, 8:22am

Hello, sorry for the late reply, but I have now found the problem. The server I am using was installed and configured by our hoster. An error was probably made with the tzdata program. No matter which time zone was set, everything was changed to cest +2 in the background. A reinstallation and reconfiguration using “apt-get --reinstall install tzdata” and a subsequent configuration using “dpkg-reconfigure tzdata” solved the problem for me. Many thanks for your help, this has put me on the right track. Another problem was the missing time zone on the server from which the backup was made. After this was also set, the data in the backup was also correct.
The ticket can be closed.

system · July 9, 2024, 8:22am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.