The definition of Unix epoch time

"Unix time, or POSIX time, is a system for describing points in time, defined as the number of seconds elapsed since midnight Proleptic Coordinated Universal Time (UTC) of January 1, 1970, not counting leap seconds.''

The key point here is that an epoch timestamp is in UTC time.

How Facebook does event times

''Note that the start_time and end_time returned 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 and 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.

My workaround/hack/fix

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:

CREATE VIEW com_palm_pim_FacebookEvent AS SELECT * FROM com_palm_pim_CalendarEvent WHERE _class_id IN (SELECT child_id FROM _ClassTree WHERE parent_id = 85);

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:

UPDATE com_palm_pim_CalendarEvent SET startTimestamp=startTimestamp-(19*60*60*1000),endTimestamp=endTimestamp-(19*60*60*1000),timeZoneId="Australia/Sydney" WHERE id IN (SELECT id FROM com_palm_pim_FacebookEvent) AND timeZoneId IS NULL;

Pretty insane, huh? I'm quite proud of it too :P

Or better yet, only update stuff in the future, beyond now:

UPDATE com_palm_pim_CalendarEvent SET startTimestamp=startTimestamp-(19*60*60*1000),endTimestamp=endTimestamp-(19*60*60*1000),timeZoneId="Australia/Sydney" WHERE id IN (SELECT id FROM com_palm_pim_FacebookEvent) AND timeZoneId IS NULL AND startTimestamp > (SELECT strftime('%s','now')*1000);

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.

Problems encountered

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!)

Improvements

Naturally, this could be cron'd.

There is a standard tzfile(5) database somewhere on the Pre, but I doubt you can do magic from within sqlite3 with it -- there would have to be Javascript involved. If I can be bothered nutting this out, that would take care of different events having been in daylight saving time or not.