Custom Date Field Returning Wrong Timezone on Email Notifications

bradgillap

Hello,

I have a custom "Help Topic" with form field for datetime.
When I fill in the forms helptopic and create th ticket.
I choose "now" for the current date/time in the form which displays correctly.
The ticket is submitted and the field shows the correct time post submission.
An alert email gets sent that references this same datetime field in the template and shows the incorrect time as if the timezone is +0000 rather than the timezone reflected in the actual ticket.
The imap and smtp works fine and times/dates are correct on email headers.

Here's some other relevant information. I'm fairly certain that I'm looking at a bug in the email alert templates for new ticket.

So date/time is correct everywhere except when the alert email is generated using this datetime form field.

timedatectl

Local time: Mon 2023-03-06 13:22:23 EST
Universal time: Mon 2023-03-06 18:22:23 UTC
RTC time: n/a
Time zone: America/Toronto (EST, -0500)
System clock synchronized: yes
NTP service: inactive
RTC in local TZ: no

lsb_release -a

Description: Debian GNU/Linux 11 (bullseye)
Release: 11
Codename: bullseye

php -v

PHP 8.0.28 (cli) (built: Feb 14 2023 18:26:48) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.28, Copyright (c) Zend Technologies
with Zend OPcache v8.0.28, Copyright (c), by Zend Technologies

/etc/php/8.0/cli/php.ini
/etc/php/8.0/apache2/php.ini

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
date.timezone = America/Toronto

mysql -v

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10723
Server version: 10.5.18-MariaDB-0+deb11u1 Debian 11

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'time_zone';

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.097 sec)

Sendmail

TonyO

bradgillap
In your response template, how do you reference the custom date field in the help topic?
I have a similar setup, Bullseye, PHP 8.1, Maria 10.5. I'm trying to reproduce this, but my user sees the correct time so far.

TonyO

If you're using fpm (php8.0-fpm) you will have /etc/apache2/conf-enabled/php8.0-fpm.conf
In this case, osTicket is using /etc/php/8.0/fpm/php.ini rather than /etc/php/8.0/apache2/php.ini

Also check Profile -> Preferences and make sure your timezone is set correctly for your agent account.

KevinTheJedi

TonyO

Also, I think the dates in email templates will be in UTC to reflect global time that can easily be converted.

Cheers.

bradgillap

Thank you

PHP FPM is not in use in this case.
The timezone on the agent profiles are correct.
I also hunted down the sendmail conf (although I think it uses phpmailer anyway) and just made sure the conf there also refers to the system timezone.

ramrajone

bradgillap
Can you check to see if the session is set to the same timezone?
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

check this post for other details: https://howtohelpdesk.com/how-to-set-time-zone-in-osticket/

bradgillap

ramrajone

Sorry, I just caught your message. I have gone and specified the timezone for both global and timezone after importing the timezones from system into mariadb.

The behavior is the same after this. Now my timezon

MariaDB [osticket]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| America/Toronto | America/Toronto |
+--------------------+---------------------+
1 row in set (0.000 sec)
`

bradgillap

The response template section looks like this.

Incident/Accident Date Time: %{ticket.incident_accidentdatetime}

No weird html or anything wrapping it.

Here are some images of the form field, and the text the email is outputing.

https://imgur[dot]com/a/L7GlWSB

bradgillap

This might help shed some light.

So looking at the ticket as an agent shows the correct time in the field. Yet the queue page does not.

https://imgur[dot]com/a/v8fXZbF

KevinTheJedi

bradgillap

Please don't post links to 3rd party sites for images...you can upload images directly on this Forum. If you post a link to an image it's likely no one will view it for security reasons.

Cheers.

bradgillap

@KevinTheJedi

KevinTheJedi

bradgillap

The variable and Queue is printing the raw time stored in the database (which is in UTC - Coordinated Universal Time). This is a universal time that can easily be converted to any timezone. Currently, there is no way to change this behavior unless you modify the code.

Cheers.

bradgillap

Great,

Thanks, I'll work around it then. This is kind of a mash of things for incident reports. We're having staff enter in incident report data into the ticket system which sends a nice template off to an email address to be picked up by Mayan EDMS and stamped into a PDF, thrown into a cabinet. That way we get all the nice tracking and tickety things. We can use things like light-dash to build stats based on the ticket system for reporting these custom fields but also satisfy the offices legal side of an official sort of sealed document of events.

So, so far it's going well. This was the only nitpick. I think I'll just do a regular date field without the time and a textbox for the actual time and not worry so much about the validation. It's not really that important for reporting anyway so long as I have a nicely validated day stamp and of course the create timestamp is there as well to get at least fuzzy reporting out of.

bradgillap

Just for anyone else

Here's what I've done and I managed to even salvage the validation.

I used a regular expression for time validation for HH:MM AM/PM 12 hour format instead on a short answer text field.

Here's the expression

^(0?[1-9]|1[0-2]):[0-5][0-9](\s?[AaPp][Mm])$

example: 12:45 PM
example2: 12:45 aM
exmaple3: 12:45 Pm

KevinTheJedi

bradgillap

I would suggest:
/^([0-1]?[0-9]|2[0-3]):([0-5][0-9])(\s?(am|pm))$/i

Cheers.

ealtman

I do not know if this helps but ever since I installed with PHP 8.0 I've had a problem with time with OSTicket picking up the timezone.

I upgraded to php 8.1, although the system information finally showed the PHP settings
Timezone =America/Los_Angeles,
there was still a database timezone problem. The "Database Information and Usage" was showing
Timezone = 0:00 Interpreted as UTC.

I then went to the my.cnf file and reset it as -07:00 and then as PDT -- neither had any effect, no matter how much I restarted mysqld, php, httpd php/fpn etc.

The one thing that worked was to log into mysql and set as follows:
set @@GLOBAL.time_zone ='-07:00';

This WORKED!
Now this means when we are out of Daylight Savings TIme in California I will have to go in and reset. I think this is all due to having used php 8 in the first place ugh. But at least my tickets and messages are no longer difficult to track.