Friday, December 18, 2009

Excuse me, what year do we have? - Some usability enhancements for XQuery in DB2

Imagine that in the XML world there is already 2010, but in your relational world it's still 2009.  This or the reverse could happen if you directly use fn:current-date() in XQuery and the CURRENT DATE special register in SQL. The reason is that XQuery by definition operates in and returns UTC-based time and date values, e.g., whereas the SQL context in DB2 refers to the local timezone.

To work around possible issues, user could call fn:adjust-date-to-timezone() to convert a data (or analogously a time or dateTime value) to a specific, e.g., the local timezone. Well, the world got simpler just recently. DB2 9.7 FP1 introduced new (DB2-specific) functions to retrieve values in the local timezone directly, named current-local-date, current-local-time, current-local-dateTime, and local-timezone.

Having different years in the SQL and XQuery context might be a rare event, but the usability improvements are very handy - not just close to year end.