The Pre, Facebook and timezone issues
Saturday, January 30 2010 at 07:28 PM EST
Contributed by: jason
A lot of people have noticed that the Pre typically shows Facebook events at the wrong times.
This article is my crack at clearing things up.
As at version 1.3.1, Synergy and the Pre doesn't display Facebook event times right. This is, in my opinion, due to an error on both the part of Facebook, and Palm.
The definition of Unix epoch time
Wikipedia rightly defines Unix_time as:
The key point here is that an epoch timestamp is in UTC time.
How Facebook does event times
end_timereturned are the times which were input by the event creator, converted to UTC after assuming that they were in Pacific time (Daylight Savings or Standard, depending on the date of the event), then converted into Unix epoch time.''
... Pacific time being GMT-8.
Why they assume this PST timezone, who knows... their backend seems to be smart enough to work out what timezone a typical browser session is in (I'm assuming via +/- offsets in
Date:HTTP requests), so why not use that when an event is submitted, convert down to UTC at that time, and all is well!
Instead, they do it this way, and do some crazy magic when others view the event. This is where I say Palm is partly at fault -- because they haven't put this same magic in. They shouldn't have to though.
All of this gets a little more confusing when daylight savigs comes into play, mainly because conversion of these times needs to be done taking DST into account, at the time of the event (and not current time).
What WebOS does
It would seem the Pre assumes that the
are in UTC via a Unix epoch timestamp -- very fair logic in my opinion, as this is what Facebook ''should'' be returning via its API. Instead, it seems to return
local_time_of_event+8 (varying, depending on your browser/Facebook timezone, the PC , and the Pre). Bizarre.
I've looked into doing things cuter inside the OS, and via GUIs, and it's all just too much pain. Instead, I've chosen a direct sqlite3 set of queries.
There is a very handy view created in PalmDatabase.db3, called com_palm_pim_FacebookEvent. Palm have done some very element RDBMS design, and using this view simplifies finding just Facebook events with which to deal.
As at 1.3.5, the schema looks like this:
I'm not sure if the parent_id changes with each phone, each restore, or if it's static. When in doubt, run sqlite3 on the database, and do a .schema com_palm_pim_FacebookEvent.
Armed with this, here's the UPDATE statement I use:
Pretty insane, huh? I'm quite proud of it too :P
Or better yet, only update stuff in the future, beyond now:
What we're doing here is updating the startTimestamp and endTimestamp fields by a precalculated number. The 19 is what I've worked out as an offset in hours from the mangulation that is the default. Yours of course will be different, and it's as simple as looking at the event time in your Pre and comparing against the actual Facebook event time. A good tool for epoch and other calculations can be found here. As the stamps are in Unix epoch time (in milliseconds), we multiply the 19 by 3600, and then 1000 to convert to milli.
Currently, it seems that Palm isn't using the timeZoneId field, so I'm using that as a marker to say "this entry has already been corrected". The theory is that the above statement could be used even in a cron event to regularly and automatically update your entries.
Note also that this does every event. Even ones that may perhaps not be in daylight saving ranges, etc. A future improvement on that is documented below. You may also want to get creative and limit the UPDATE to certain epoch time ranges. Some modification on SELECT strftime('%s','now')*1000; would do it.
This is naturally frought with danger, but I suppose torching the Facebook account and re-adding would restore things.
The first issue I found was that the Calendar app would update randomly and weirdly. The month view doesn't seem to update at all (I suspect WebOS needs to be told to rescan, or the monthly view in the app has it's own DB view that needs touching). Actual events will show the new time though, and that works for me.
Weekly view seems to update as well, after you either view the event, or switch calendar filter options back and forth. Or exit the Calendar app for a while. I honestly have no idea.
I seem to recall that there was a WebOS update for 1.3.5 to speed up Calendar views -- some form of caching. This is probably the culprit.
(I just did an emulator reboot, and that definately refreshes all views and works. Restarting Luna itself doesn't fix it, so there's obviously some other DB reload or something that fixes the caching -- help welcome here.)
(I just did all this on my real GSM Pre, and it seems to have updated straight away, joy!)
Other handy SQL statements
Some of these were handy in my travels.
This shows a more pretty-printed output of your Facebook events:
This is handy to just pull out a single event:
Naturally, this could be cron'd.