Recurring scheduling didn't work with non-UTC timezone

Hi tipjoe,

Could you just confirm something for me?

- Your Ubuntu server is set to the Denver time zone
- Your Xibo admin interface is set to the Denver time zone
- Your client PC is set to the Denver time zone

I think there may be a few inconsistencies in the way Xibo is working with dates...

The Start and End time you enter for an event will be recorded in the DB with no conversion at all (meaning the dates you put in will be considered as the same time zone as the server).

The Start and End times of all the recurring events you set will be compared against the time zones of the server and the Xibo time zone setting.

On the way out (when you look in the calendar) these dates are pulled back out and presented exactly as they were recorded - therefore highlighting the problem.

I'll be looking into this further - with a view to improving it for the 1.0 Series - hopefully its not a massive change!

If you could confirm your exact settings for the above that would be very helpful! Thanks

After more research I think it is likely that your Ubuntu server is in a different time zone to the one you have selected in the admin interface (I might be wrong here - its only a suspicion).

Xibo should support this - but it actually doesn't!

The solution is a re-modelling of that way Xibo handles dates - not an easy fix!

Sorry for the novice mistake. I changed my server time zone and all is great. I don't know that this is worth putting a lot of time into changing since the solution is so easy.

Thanks for the fast help.

Sorry I spoke to soon. I've confirmed that the timezone settings on my server, client, and xibo admin are all set to America/Denver. When I save it an event, it subtracts the start and end times by exactly 12 hours (i.e. 08:00 becomes 20:00 of the previous day).

Here's what I can see when all the timezones are in sync (xibo admin, server, client) using my UTC-6 local time:

In the database, the schedule table saves the actual times I enter, but in the schedule_detail table it saves what I enter minus 6 hours (my utc offset). Then when it displays on the calendar, it thinks the times in the schedule_detail table are UTC, so it applies my timezone offset (-6), making it display exactly 12 hours off.

I think the solution is when the times are written to the schedule_detail table, they need to be converted to UTC. The code appears to expect that, but currently, it's calculated what I enter MINUS the offset. So if I've set UTC-6 (America/Denver) in my xibo admin settings, then it should take whatever I enter and save it as that PLUS the offset to make the UTC. Then, when it displays on the calendar, it will take the UTC and subtract the offset, showing the correct time.

Next, if the actual UTC were saved in schedule_detail, does the client also assume that it should take the schedule time minus the offset like the calendar?

The current workaround seems to be setting the client, server, and xibo admin settings to UTC. That way, no offset affects them. But then the scheduler has to schedule using UTC rather than local time.

You have certainly hit on the reason for the failure - the dates written to the schedule_detail table fail because of the way the original dates are read back from the schedule table. Here is what happens:

1. The dates are written to the schedule table exactly as you provide them
2. The schedule dates are read back using UNIX_TIMESTAMP from the DB << this is part of the problem
3. The dates are passed through some time zone aware PHP functions (which take the time stamp and apply your time zone offset) and saved to the schedule_detail table
4. When read back to the calendar month view the same occurs again - so you get the double offset applied.

As you have rightly said the solution is to write the dates in a base format (UNIX time stamp works best as far as I can tell). You are also right to mention the Xibo client - it will also need to be time zone aware!

There is one last thing I am confused about - I would have expected you to see this behaviour scheduling normally (as well as recurring)?

I am half way through changing the scheduling for 1.1 series - I will need to discuss with Alex whether I can make the same changes in 1.0.2 or not (there is a database change required). If not you may have to move to the 1.1 series (which has some experimental code) - or failing that I think I could easily provide you with a Patch.

I suspect I will have it fixed in 1 weeks time.

Thank you for your help!!

In fact - I may have a code change to 1.0 series that would fix it for you!!

Let me do some more testing and get back to you with a file to replace.

Cheers,

Dan

When you noticed that your Ubuntu Server time zone was incorrect and changed it to Denver - did you also restart your MySQL server?

I am sure the problem is with the MySQL UNIX_TIMESTAMP function converting the time stored in the DB to a time zone other than Denver...

The reason I ask is that I doubt this can be fixed easily with 1.0 - it is simply used (wrongly) in many places. I have 1.1 code / database changes that fix this problem, but I obviously need to push this code into all the places where it is used - and test it thoroughly. Which may take a few weeks!

Cheers,
Dan

I think the mysql restart did the trick. Thank you!

p.s. I was seeing the problem on non-recurring too, but just happened to be testing recurring at the time I submitted the bug. I'll try to be more thorough before submitting.

Joe

Hi Joe,

Glad to hear that worked for you! And thanks for reporting the bug - it has shown us some really good areas to work on in Xibo. If you stay subscribed to the bug you will be notified when it is properly fixed in 1.1

Cheers,
Dan