Friday, April 8, 2011

Some more time, some XML functions

I had written about some details regarding current time and timezone for the regular DB2 and the Oracle mode. Now I had some time to play with built-in XQuery functions.

There are quite many that deal with time, date, timestamps, and timezones. For the XML and XQuery processing an implicit timezone of UTC (Coordinated Universal Time) is assumed. This is something to keep in mind when processing XML data - different semantics again (who said life is easy?). XQuery defines a good number of functions and operators on durations, dates, and times. DB2 supplements that with some more functions to make life simpler (not easy) in DB2. Most of them provide the local value, i.e., the one related to where your database server is located.

xquery current-dateTime()

Note the "Z" behind the timestamp, indicating Zulu (UTC) time, not necessarily the one of your location. DB2 adds its own function to deliver that:

xquery db2-fn:current-local-dateTime()

Now the "Z" is missing and we have a timestamp without a timezone. How about some experiments with timezones?

xquery db2-fn:local-timezone()

xquery db2-fn:current-local-dateTime() - current-dateTime()

With the first call we can obtain the timezone at our place. In my example it is the Central European Time with Daylight Savings being active. Not surprisingly, subtracting the (global) current time from the current local time, we receive the same difference in hours.

xquery adjust-dateTime-to-timezone(current-dateTime(),  db2-fn:local-timezone())

XQuery has a function to adjust a timestamp to a specific timezone. In the example above, we use the already known functions as input and the result gives another timestamp. The interesting part about is that now a timezone indicator is returned, "+02:00".

My time is up for today. If you have time, try out the other time-related functions in XQuery...