phlyMail Support Board

[HumbleScholar]

The Timezone setting in Setup/Programme is set to Australia/Melbourne.
When I create an event, the start/end date/time is correct for my timezone, however the reminders I set pop up almost a day later than they should.
Given the 18 hour difference between my local time and my server's time, I'm guessing that for reminders the program is checking the server time.
The Upcoming Events window on the pinboard also keeps displaying events that are up to 18 hours old.
I tried fixing this by setting the default timezone in .htaccess to Australia/Melbourne but it didn't seem to help.
Hope this one's easy to pinpoint.
Cheers,
Alik

[mso]

Hi Alik,

thanks for reporting that issue. It never came up before, probably mostly because of the fact, that most users have a server in the same timezone.
As a blind guess I'd say, that the timezones of PHP and MySQL differ. Let's try to address that issue by a patched database driver. Please copy the attached file to phlymail/shared/drivers/mysql/ - after doing the usual backup of the old file.

This patch aims to align the timezones of both PHP and MySQL. You might experience a shift in the displayed starting / ending times of the already stored events / tasks. I recommend to backup your database to evade that issue.

[HumbleScholar]

Unfortunately the updated driver did not solve the problem.
Maybe the script is using some configurable variables or some version-specific php functions?

[mso]

It is not really version specific. But the settings done to PHP require at least PHP 5.1.0 and the ones now done to MySQL 4.1.3.

I tested the changes beforehand and instantly noted a difference: After setting the phlyMail timezone to a different value, all events in the frontend were displayed in that timezone. I did not check though, whether the alarms went off at the right time. Did you check that?

[HumbleScholar]

Maybe this will help:
I went to add a test event and set a reminder. It showed in the Upcoming Events with the right date/time.
When I went to the Calendar the event date/time shows shifted forward by the timezone difference.
They should both show the same date/time.

[mso]

So...

After looking deeper into the issue, I've unfortunately got bad news. The whole MySQL abstraction is not really prepared to operate on a timezone different from the one, the server runs on. The DB queries mix the use of timestamps and dates wildly and aligning them will take some time.

Some info about the background of the issues: There's (basically) two fundamentally different ways to get dates to and from MySQL.

One is using UNIX timestamps, which is an integer value representing the seconds since 1970. This approach is independent of any timezone issues. But it has one big disadvantage: One cannot store values before 1970-1-1 in the DB. That's basically OK for storing the next appointment with your boss but fails miserably storing the birthday of one's grandma. Although one can roughly iterate through a certain time range by simple math, it is not very easy to address a specific point in time like "April 15th 1982".

The other approach uses date/time values (2010-03-21 10:00:01). The range of these values is huge and spans from the old ages to the distant future. It's easy to address specific dates ("Give me all appointments from December 1st next year to December 31st"). But the major disadvantage here is, that the date values always depend on the current timezone.

Mixing these formats is no problem - as long as the server timezone, the session timezone and PHP's timezone are the same. As we can see clearly now, all goes south, whenever one of the three has a different timezone. Obviously it's not sufficient to force PHP and the MySQL session to share the same timezone info.

I'll have to look very deep into that.

[mso]

To add to my last post: Do you see any chance to have the timezone of your server aligned with your own one?

[HumbleScholar]

Hi Mattheas,
I could theoretically move my hosting to a local provider but it would cost me more than having to do without reminders .
I've looked through the system settings and did not find the ability to change the server timezone.
It seems to me that without that setting this problem cannot be solved reliably.
Even with the server timezone setting, a more elegant solution would be to introduce querying of an online time server.
Either once a session for alignment or in real time, querying of a time server will avoid the annoying problem of the daylight saving adjustment.
I am not aware of other calendars that solve this problem effectively though I suspect that Google calendar probably does.
Something to think about.
Thanks for trying to help out. The reminders are good to have but not life threatening.
Still this issue needs addressing before the product can go international.
Cheers,
Alik

[mso]

A few notes to your post:

First, it has nothing to do with the time of the server shifting from real time. Querying an NTP server is outside the scope of phlyMail and needs to be done anyway by the server's administrator.

Besides, you should not need to physically move the server, it should suffice to adjust the timezone setting of it to reflect your location.

If both - querying the time server(s) and the timezone setting - are correct, switching DST will happen automagically, since time zone information includes the handling of these.

I am right now sifting through the code and working on a nice solution to handle timezones perfectly in the future. The solution itself is fairly simple (in principle), but requires a secure algorithm to transfer all the events users might have stored to the new system. There's installations, which have a few thousand events stored, inadvertently shifting these a few hours would... well... you figure

The solution will be, to store all date / time values as UTC and do the conversion from / to the local timezone in the frontend only. If need be, the timezone setting of MySQL will be adjusted to reflect that. As said, I am right now working on it, but don't hold your breath, though. It will take me some time.