ISO compliant year-week function in DB2

 I've already shown how to create a year-month function in DB2, which - when it comes to date arithmatic - is quite straightforward, because very year (in ISO/Gregorian) calendar starts with the first month.

Some systems argue whether this should have the ordinal 1 or 0, but thats the usual 0/1 issue in programming.

Weeks however, are far more complex, because not every year starts with the begin of a week (whether this is Sunday or Monday in your preference / area).

It might just start with a Thursday... WOW.

So for that ths ISO 8601 standard set a definition on what is to be considered week 1 of a year:

The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. of January) in it

Luckily, DB2 has a function for that - WEEK_ISO.

So let's just try that with a 

rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)

Takes the year (need to rtrim it) and adds 2 digits week to it (you might want to insert a "w") between them.

However, this leeds to e.g. 2021-01-03 being in week 53, because week 1 start on 2021-01-04.

the yearweek for 2021-01-03 therefore should be 2020-53 not 2021-53 as the above formular would yield.

Now we need to make sure that if we get a week 53 and its January we return the previous year... Only for January, because some days in December might also be week 53, and we need to keep the year there.

Voila:

create function yearweek_iso(TS timestamp)
returns varchar(6) no external action deterministic 
return    
CASE

     WHEN (week_iso(TS)=53 AND month(TS)=1) Then
              rtrim(char(year (TS)-1)) || right(digits(week_iso(TS)),2)   
     ELSE
              rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)
END

The results now match whatever java.time package might do with week parsing. In order to get the first day of this week back (in Java, where I needed it), you parse as follow:

new DateTimeFormatterBuilder()
            .appendValue(IsoFields.WEEK_BASED_YEAR, 4)
            .appendValue(IsoFields.WEEK_OF_WEEK_BASED_YEAR,2)
            .parseDefaulting(WeekFields.ISO.dayOfWeek(), 1)
            .toFormatter();

Google .new shortcut for Calendar

Just found this on engadget.

If you type in cal.new or meeting.new in the address bar (sorry, awesomebar, or whatever it is called these days), it will directly re-direct you to the page/dialog to create a new calendar entry for your google calendar.

Seems to have been around for google docs for a while now, but I don't really create a lot of them, compared to calendar entries.

Nice use of TLDs ;)

New Google Calendar Provider for Thunderbird/Lightning

Finally a Google Calendar Provider for Thunderbird/Lightning has been released that's based on the gdata api, and not just a network XML link.
Problem with the later approach was - to me - that you needed to add each of your calendars separately via their URLs. That seemed to have performance impacts as well. I had about seven such calendars, and it significantly (with my other CalDAV calendars) slowed down Thunderbird start-up time.

When the new provider was released a couple of days ago, I first really noticed, because it forced me - with a separate pop-up - to authorize it for my Google account. With seven calendars this step kept popping up - seven times, of course - but still for the old calendar URLs.
Performance was really nasty...  Thunderbird was unusable essentially.

For some weird reason, I tried a "New Calendar" > "On the Network" > "Google Calendar" and found, that it was actually letting my select Google calendars by account, instead of just letting me add the XML link per calendar like the previous version.

So first thing, delete all my old Google calendars in Lightning, and try the "New Calendar" > "On the Network" > "Google Calendar" again, with my Google account, and voila, it presented all the calendars I had on Google. With the color as on the web. I only needed to select the ones I wanted in Lightning, and that was it.

Should have been like this from the start :)

Thunderbird start-up performance is back to a couple of seconds now (from about 1 minute+)

Lightning Drap'n'Drop Problem

In my earlier post (and comment discussion) about Opening ICS Files with Thunderbird/Lightning we mentioned that you can also drag the .ics link directly from the browser to the calendar pane in Thunderbird/Lightning.
I also found, that this is not always working.

So - as promised - I looked into this a bit.
First, narrow down the "not always" to something reproducible:

I took a link that I knew was working with drag&drop (actually from the timetable of the Austrian Federal Railways), and verified this. Yep, working nicely

Then I just downloaded the resulting .ics file to my local hard disk into a folder that was mapped into my webserver (Tomcat 7). I wrote a tiny .html file that mainly included a link to the .ics file.

Then open the html file in Chrome/Firefox and drag the download text/link into (or is it onto?) the Lightning calendar pane in Thunderbird. Ignore the second line for a second (no pun intended).

Nothing.

Well, that was surprising... So I went on to waste a lot of time with checking the mime-type etc etc.

But it was fine by responding with "text/calendar" - see:

So I took some Java drag&drop code I had, and took a closer look at the data included in the DnD object. Also everything fine there (I spare you the details here).

By chance I looked into the access log of the webserver and found the following:

GET /test/download.icsdownload HTTP/1.1" 404 1001

Which essentially says, that Thunderbird requests a broken URL. It should GET /test/download.ics and not /test/download.icsdownload

No wonder it gets a 404 and cannot import anything.

Quick test to include a "?" in the a href (see above, the second line I asked you to ignore earlier).
If you drag this one, you'll get a
GET /test/download.ics?download? HTTP/1.1" 200 949



As we are handling static files here, the "?download?" appended to the filename will gracefully get ignored and the file will get served (as can be seen by the 200 response code)
And with this link, Thunderbird really imports the file and opens the event dialog.

Fine there.

What Lightning does, is - wrongly - append the text of the html anchor ("download" or "download?") to the URL. You can test with naming the anchor asdfgh and will see that /test/download.icsasdfgh will be requested.

Why does it do it ?

It seems to be that Lightning picks up the HTML fragment data type that gets passed by DnD. The browser passes the full anchor, from begin tag to end tag (see above), and Lightning stupidly parses until the first newline (\n), and throws away the HTML tags.
This is wrong.
Should not happen.
I'll file a bug in bugzilla.

CalConnect: 7 Things You Should Know About Tasks

One of those conundrums [1]: The more popular mobile devices get, the more people care about data interchange. Don't ask me, why they did care less on PCs... Probably it is easier on a PC to read and re-type a calendar event, than on a smartphone.

So, I'm glad to read that the CalConnect consortium is doubling their efforts on Task (todo) exchange/interchange/compatibility.

CalConnect: 7 Things You Should Know About Tasks

--

or is it conundra ?

Opening ICS Files with Thunderbird/Lightning

Now as the world is slowly progressing towards the 21st century when it comes to calendaring tools, some websites offer to download a calendar file (ical, ics, ...) for events they publish.

The problem in my environment is, that no application on my (Windows) PC is registered to handle this file type.
Thunderbird/Lightning simply does no do this.

The out-of-the-box solution without any hacking is to simply download the .ics file e.g. to the desktop, and then drag&drop it to Thunderbird's today pane. This triggers an import. Quite OK.

Works with TB17 and Lightning 1.9.
(Sometimes it seems I have to hold the Ctrl key to force a copy (instead of a move)... awkward.

CalDAV for Android

I've been looking for this since my first Android last year, but at that time there was no CalDAV support for Android. So I had to use the Notify Active Sync solution my company provided. This basically allowed to sync the calendar (and email, ...) as ActiveSync (from the handset's point of view) to our corporate calendar/email/...

With the re-installation on my new HTC One S I found that there is a ("native") CalDAV sync for Android, and luckily our corporate calendar supports CalDAV.


With CalDav-Sync beta from the Google play store you create - as expected - an additional calendar and set up a new profile in "Accounts & sync".

You can change the sync interval, the range of events to sync (past and future) ...
There are also some kludges and fixes for weird calendar or phone behaviour, e.g.  that some Android phones seem to create events only as tentative.

The default mode is a one-way sync from server to phone only, but all you need to do is disable this option, and - voilà - you have a two way sync from server to phone and vice versa.
I have had this running for about a week now and it works like a charme... Although I have to admit that I did not try any synchronization conflicts yet.

Finally someone publishing calendar data

Baby steps, baby steps... both to proper publishing of calendar data and to open data in government/administration.

The municipality of Vienna publishes the calendar of public school holidays for this year as an iCalendar file... (here at
Schulferien in Wien im Schuljahr 2011/2012; German only).

For non-nerds they go so far as to explain what an ICS file is: "for integration with e.g. Microsoft Outlook, Ical or  Google Calendar".

Nice.
Let's hope this is not a one-time, static file for 2011/2011 only, but will become a stream of school holiday data...

Thanks, Martin, for the pointer.

Google Calendar Goodie

Have you ever noticed that Google Calender shows the current day (of the month) as the favicon.

Therefore also in the tab (on Firefox), even it the tab is pinned.

Cool. Nice.
Not sure if it is entirely useful, but I like it.

iPad/iPhone date dialog anomalies

It is really odd how developers use the iOS datepicker[1]...

The built-in calendar app - which has many other shortcomings  - only lets you enter the time in 5 minute increments. Which is - usually - OK, but when you want to enter e.g. the departure time of a plane or train, you might want to enter the exact time.

On the other hand, there is Qando, a service (with an app) that lets you check the timetable of public transport in Vienna. Oddly enough, when I want to specify a (rough) time for departure (or arrival), it lets me pick the time in 1 minute increments... the one place where e.g. 15 min resolution would be fine....

This really is annoying - it make the time selection (e.g. when you want to jump to 17:30 = 5.30pm)  a lot slower, because it makes you scroll through 30 values...

Did you also notice that the datepicker is not correctly localized? Almost everything here is proper German ("Jän" = Jänner for January, "Di." for Dienstag = Tuesday), but it still quite prominently says "Today" instead of "Heute".

--
[1] the dialog/control which lets you enter date & time on your iPhone/iPad/iPod

Enabling Multiple Google Calendars for iOS

Found the following tip recently:
iPad Quick Tip: Enabling Multiple Google Calendars: Apple:

quick version: if you go to http://www.google.com/calendar/iphoneselect you can select which of your Google calendars will be visible on your iXxxx (iPad, iPhone, iPod, ...)

Previously I had to manually copy/paste the various caldav URLs from Google to the iXxxx, which is quite cumbersome.

This here is a lot easier.

Date - sortable

This is to all the people who include a date in a file name or document name (and there are good reasons for doing so... simple way of versioning).

Is this really so hard to format a date so that it is sortable?

That would be year first, then month, then day (of month) - just like ISO 8601 says it should be. year-month-day or year/month/day ... whichever separator you prefer...[1]
And please - numeric only.

So please, do not include a name of the month, because then April appears before February. And the sort order would be locale/language dependent. Not a good idea.

So no "Apr-27th" in a file name please (I've seen that).
Just make it "2010-04-27".

That's sortable. Everywhere.

Come to think of it: in that regard the rather awkward American way of writing a day (month/day) does make sense all of a sudden... still month/day/year is totally pointless.

Thanks for listening.

You can go back to what you were doing.

--
[1] but stick to it. No point in using - in one file name and / in the other.

Scheduled Sync for the E71

Just when I was about to rant about the lack of periodic or automatied calendar sync on the E71 (over-the-air/OTA via SyncML) I did in fact google for it and found that the Bergamot project on Google Code does provide exactly this.

The program is called SWIM and this is what it does:

Swim is a utility for automatic periodic synchronization of data with internet servers such as Google Calendar (through GooSync), Mobical, Zyb, ScheduleWorld, Ovi or any system running Funambol. It makes use of the existing SyncML support in the system, and adds timing capabilities, something both UIQ and S60 mysteriously lack! You can set it to sync your data every 15 minutes, every hour, every 4 hours, every 12 hours, daily, or weekly.

Swim uses the existing Synchronization functionality and profiles from the E71... which is cool, so there is no difference if you sync manually through the menu or run the synchronization scheduled.

The how-to section is quite ok... here are some tips and tricks from my installation, though:

• For my E71 I needed version 0.41 from the download section. The more recent v 0.50 did not do anything.
  The tricky part is, getting the beast signed using the Symbian Open Signed Online tool.
  This is also well explained in the How To Install section: just upload the unsigned .SIS file, download the signed .SIS file and install this to the phone.
• However, I ran in to the problem that my phone said that the certificate of the signed .SIS file was “not yet” valid. A google search for that revealed that this is a common problem with - as it seems - the clock of the signing server and the phone being too far out of sync. The trick is, to put the phone 1 day ahead, install the software, and the put the phone back today.
  Frankly, I don't really see why I should (self-)sign software at all, if the only way to install it, is with a kludge like that.
• Anyway, after that it ran just fine, I put it to 30min synchronization interval for starts and thats what it did... After a day of observation (without any problem) I put it to 12hrs, which is sufficient to have my corporate (Sun) calender synched with my phone...

So Hooray and thanks to the Bergamot project...

Towel Day

I (again) totally forgot about Towel Day this year until just after it ended... probably because I was offline that day (in Scotland).

So in order to always remember it, I created a (public) GoogleCal entry for it. You can subscribe to it via this button ...

And in doing so, I noted that when you publish a recurring event on GoogleCal (like I did, see the Google button above) it does not carry the recurrence information with it...
Stupid.

Thank you, twitter

Another reason to like twitter ... or the guys behind twitter: in their announcement/warning of the upcoming downtime, they linked the time to timeanddate.com, so everyone can see it in their own timezone.



Since I receive a lot of event invitations from the US I know that his is quite unusual... totally the exception.
So, thank you, twitter.

m³: Silver Bullet Wiki?

Martin has interesting observations (and rants) on Wikis as tools for building/negotiating an agenda for a meeting.

m3s online Pamphlet :: y2008 : m02 : Silver Bullet Wiki:

Instead of emailing a meeting agenda, put it on a wiki page and email people a link to that page. If changes need to be made, anyone on your team can do so and everyone will have immediate access to the same, up-to-date version
[...]
And how does that differ from a Word file on a fileshare, a Sharepoint place, an Exchange shared folder or a Lotus Quicker?
A fool with a tool is still a fool. If people are not used to working on ONE copy of a document – no matter what technology – they will still end up with several versions of the document. Either by printing it to PDF, copying into the calendar-entry, etc.
[some typos corrected]

But Martin, why so strict ?
I'd understand "wiki" as a pars-pro-toto for all of the options you suggested.
I strictly oppose to the Word file (or ODT file for that matter;-)) on a whatever server.

Cool would be:

1. A "document" (not word) on a server -> then you get the single copy property.
2. directly editable through web and an API -> allows for easy and direct access from all possible tools
3. obviously you want the author/editor of a change/addition/suggestion to the agenda visible and identifiable
4. And then you'd want both an RSS/Atom feed from that document as well as an iCal/webcal/caldav access to this document. (And others: you might want to generate your PDF directory out of it for archival and print).
   
5. It should of course carry the whole event/appointment data as well.

That's what I'd want to see... If that's then implemented in Exchange, SharePoint, Domino/Notes or on a Wiki - I couldn't care less.

BUZZER

Wrong, sorry. I do care.
By my nature I'd prefer the Wiki or Notes approach over any Exchange/SharePoint approach... not so much religiously, but they tend to guarantee more open access - meaning both users, and APIs.

Dopplr and Lightning

I just noticed that Dopplr (the social travel site) lets me subscribe to my trips in iCal.
I did so from Lightning, the Thunderbird calendar plugin, and it works fine.

So I know have my office calendar, my Google Calendar and my Dopplr trips all in Lightning.
Cool (if somewhat redundant, though).

Mozilla Lightning 0.7 has been released

Major steps for the Mozilla Calendar project:

Lightning 0.7 & Sunbird 0.7 have been released

Lightning in the plugin (or add-on as they are now called) for Thunderbird.
(There's a standalone version, too, called SunBird).

I've been using betas and nightly builds for (almost) the last year now, and it is really quite stable.
Can't live without it anymore.

Great job, guys.

Now would you please fix the bugs I reported ;-)

Schedule meetings with Doodle

Today I stumbled upon a great service (or was reminded of it, to be honest) to easily schedule meeting across organisations.
Now don't expect something fancy with calender synch and free-busy-scheduling etc... nothing like that.
Just a simple web-based tool to facilitate the coordination and polling for a meeting date & time.

The service is called Doodle and works like this.

1. Create a new appointment like this:
   


2. Select one or more (!) dates you'd like to propose for the appointment.
   
   


3. Fill in the time slots on those days
   
   


4. Save the event and use the first link provided by Doodle and copy-paste it into the email you send off using your e-mail client with any message you like.
   
   
5. The recipients will then eventually receive your email, click on the link provided (see above) and see the status of the poll like this:
   
   


6. Then they simply fill in their name and check or uncheck their preferences/votes and that's it.
   

Quite easy, right ?

And even more important:

1. You don't have to register for the service.
2. The recipients don't have to register for the service.
3. You don't have to give the email addresses of the recipients to Doodle, because you're not using Doodle to actually send the email.
4. You're quite spam-filter-proof, because you are sending the invitation and not a internet service.
5. No technical stuff with .ics/ical/... calender formats and synching, nothing. Plain simple.

I really like that service.

(Of course it would be great that once the meeting has been voted for and decided upon you'd get an iCal file to import into your calendar... but I'd rather prefer it that simple...)