System timestamp incorrect

dlarejsalvatore

As i was checking my dashboard logs, i found that my date-time on OsTicket is added by 16 hours. Even the previous records/transactions were adjusted on date and time. I've checked my server, MySQL, and PHP time, nothing has changed. What might be the problem?

Server: Ubuntu 22.04
OsTicket Version: 17.5

e.g.
Server DateTime 2024-01-04 08:42 AM
System DateTime 2024-01-05 12:42 AM

additional note, i installed PHP ldap and intl on my server for another system hooked up on the same server via virtual hosting. they are using same PHP and MySQL version.
Thank you in advance

dlarejsalvatore

update. tried uninstalling and installing back again the PHP yet nothing changed.

KevinTheJedi

dlarejsalvatore

Your db timezone should be UTC to easily convert to any timezone. Also, maybe it’s simply an issue with that particular timezone. Try setting your system timezone to something else that’s in the same zone. You should also check your profile timezone.

Cheers.

dlarejsalvatore

KevinTheJedi I tried what you've said, i adjusted it from Asia/Manila to Asia/Taipei and it works fine now. But all the previous records and transaction date-time are adjusted. What might be the cause of this?
Note, i haven't change a settings on the system or so.

dlarejsalvatore

KevinTheJedi I tried to replicate this issue. As i mentioned above, i uninstalled PHP and all of its packages. Now, as I've fixed it, i installed the PHP LDAP and INTL and the issue is back again. Corrected it again using the method you instructed.
Is this an issue on PHP INTL adding 8 hours on the current system time zone +8 hours making it additional 16 hours?

KevinTheJedi

dlarejsalvatore

What I assume is your database timezone is not UTC so the dates were saved in a specific timezone in the db and it’s being converted from that to your system timezone of Asia/Taipei.

Cheers.

KevinTheJedi

dlarejsalvatore

To see the timezone of MySQL run the following query:

SELECT @@global.time_zone, @@session.time_zone;

If it’s not UTC then I’d highly recommend setting your MySQL timezone to UTC (make sure you persist the changes so it doesn’t revert after a restart) and then you can run queries on any date columns to convert the existing dates to UTC.

Cheers.

dlarejsalvatore

KevinTheJedi

dlarejsalvatore

I can’t really tell as I cannot replicate the issue. You could debug in your instance to see.

Cheers.

KevinTheJedi

dlarejsalvatore

Yea your system timezone is Asia/Singapore so MySQL is using this specific timezone. You should set it to UTC. Databases that store time that will be converted to other timezones should ideally be in UTC so it can easily be converted without issues.

Cheers.

B14CK_H4WK

I had the same issue, i had to make sure SQL and Server time were both correct. There's a SQL command to set the correct time and date.