A very clever function ….

This one doesn't quite relate to BI, but here is a function I hadn't seen before: new_time . This function accepts three arguments: A date, the timezone this date should be considered in, and the timezone you want the return value to be in. This can come in handy in some situations, but there is a catch. It only supports the US timezones, as well as GMT! So if you are anywhere east of Greenwich you need to resort to other measures. But really, why put a function in to a production release that deals with timezones but only a handful?

My original problem was to convert a unix time number (number of seconds since 01 Jan 1970) to an Oracle date. A quick Google brought me to AskTom . He does point out that my timezone might not be supported. His solutions is as simple and clean as expected, but DST doesn't allow for the quick fix of just adding the "gmt offset".

The link above, to the Oracle documents, does point to an example of using FROM_TZ,CAST and AT TIME ZONE functions to get this done. But back to the original NEW_TIME function: why not add the rest of the timezones in there? Here is one way of doing this (try using the values 1183680000 and 1168041600)

SELECT FROM_TZ(CAST(TO_DATE('1970-01-01', 'YYYY-MM-DD')+ 1/24/60/60 * :theTime AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Brussels' "Brussels time" FROM DUAL;