In New Zealand, it's a quarter to two too

There is a 12 hour difference between New Zealand Standard Time (NZST) and Greenwich Mean Time (GMT). Bar daylight saving, this is mighty convenient as I do not need to think about what time it is back home prior to making a phone call. Put another way, if it’s 1:45 in London, in Auckland and Wellington it’s a quarter to two too.

There is a 1 hour difference for Madrid/Spain, a mere 2hr 20min flight from Heathrow, but Lisbon/Portugal, roughly the same flight time in the same sort of direction, is GMT.

When programming for time zone differences in a database, it might be tempting to add on an hour here or there knowing the offset, with the assumption of course that daylight saving dates are synchronised around the globe (and they’re not). In Oracle, I often catch myself out adding -1, +2, -12 etc to a DATE or TIMESTAMP hiding the real meaning of the offset. I do this as converting between dates and times in Oracle is a real pain, often littered with Oracle gumf; for example, to calculate the hours difference between London and Madrid, it might be tempting to write something like this:

  SELECT EXTRACT(
                  HOUR FROM CAST('01-jan-2016' AS TIMESTAMP) AT TIME ZONE 'Europe/London' 
                           - CAST('01-jan-2016' AS TIMESTAMP) AT TIME ZONE 'Europe/Madrid'
                )
    FROM dual;

I wrote my words above carefully; this code will yield you you the ‘hours’ difference. You will have to write sometime similar for the ‘day’ and ‘minutes’ too, and convert the whole mess into a number so you can add/subtract from a date and use the result value in a meaningful way in your business logic. As this juncture, it will be apparent that the code will start to bloat out into a messy blur and you will be caught up in code that has nothing to do with the business logic, more coercing Oracle into doing something that should easily be implemented out of the box, and with the DATE data type (that embodies the time too) not the TIMESTAMP data type (and no, this argument isn’t about database design and using the right data type for the job).

Oracle is littered with other functionality to aid in time zone manipulation, some of it quite odd. For example function TZ_OFFSET can be used to provide a concise way of determining the time difference between London (my default time zone setting) and Madrid as follows:

SQL>
SQL>
SQL>  SELECT TZ_OFFSET('Europe/Madrid') FROM DUAL;
TZ_OFFS
-------
+01:00
SQL> 

At first glance, this function seems as though it is a very handy, just what I need you might think. Let’s scratch the surface. Oh, the function result isn’t a useful Oracle datatype – I’ll have to convert/coerce the function result it into a numeric value so I can use it arithmetically 🙁 This really is a sad state of affairs.

But, Oracle NEW_TIME function to the rescue, the panacea to my problems, perhaps? NEW_TIME converts between an Oracle DATE data type from time zone 1 to time zone 2, and returns an Oracle DATE data type too, something useful and familiar. Great! Here is the railroad diagram from the Oracle documentation.

How much simpler can things be?

SQL>
SQL>
SQL>
SQL> SELECT NEW_TIME(sysdate, 'Europe/London', 'Europe/Madrid')
2 FROM dual;
SELECT NEW_TIME(sysdate, 'Europe/London', 'Europe/Madrid') * ERROR at line 1:
ORA-01857: not a valid time zone
SQL>
SQL>
SQL>

Ha! Why the error? These are the same time zones I have used above. Exactly the same. What have I done wrong? Perhaps I should be using the time zone abbreviation? I’ll give this a try.

SQL>
SQL>
SQL>
SQL> SELECT tzname, tzabbrev
2      FROM v$timezone_names
3        WHERE tzname in ('Europe/London', 'Europe/Madrid');

TZNAME              TZABBREV
-----------------   ------------
Europe/London       LMT
Europe/London       GMT
Europe/London       BST
Europe/London       BDST
Europe/Madrid       LMT
Europe/Madrid       WET
Europe/Madrid       WEST
Europe/Madrid       WEMT
Europe/Madrid       CET
Europe/Madrid       CEST

10 rows selected.

SQL>
SQL>
SQL> SELECT NEW_TIME(sysdate, 'GMT', 'CET')
2 FROM DUAL;
SELECT NEW_TIME(sysdate, 'GMT', 'CET')
*
ERROR at line 1:
ORA-01857: not a valid time zone
SQL>
SQL>

Give me strength! It is time to refer to the Oracle documentation on the NEW_TIME function. Here is a verbatim excerpt from Oracle.

Really? So these are time zone abbreviations according to the v$timezone_names query shown above, not time zones, and secondly, what is in there for Europe – nothing. This function is bloody useless, and that is a sanitised expression for how I feel. The more I use Oracle, the more I feel at times it is completely justified to throw in the towel,  litter your code with magic numbers, something I wrote at the top of this blog article I am loathe to do yet catch myself doing, and something any developer from the 1960’s knows is a bad thing to do. Really Oracle, why implement NEW_TIME like this? It is another another half job – I documented an example of this yesterday too. I feel so embarrassed for you, I really do, and not because of this specific issue, but because this isn’t a one off.

So, in summary

• in Oracle anyway, I cannot demonstrate in a trivial piece of code that the difference between GMT and NZST is 12 hours, in a way that could be used in production code using an Oracle DATE data type, but just trust me, when it’s 1:45 in London, in New Zealand it’s a quarter to two too.
• you’ve seen me play with a bit of to, two, too grammar. English grammar can be fun, but not as much fun as German grammar. More on this another time.

All code snippets demonstrated above were executed against an instance of Oracle as shown below:

SQL>
SQL>
SQL> SELECT *
2 FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>

 — Published by Mike, 12:10 07 February 2017