#include <time_rant.h>

The question

"What time is it?"

Is a simple one, right? Wrong. Well, sometimes it's simple, and sometimes it isn't. The trouble is, there's some implicit context in the question: The question you typically answer is:

What time is it, in the timezone currently in effect at this location?

This implicit context is not important when J Random Stranger stops you in the street and asks you what time it is. After all, both you and they understand the question being asked, and the answer being given, and the context in which this exchange occurred. Unfortunately, this isn't always the case: When you store a time, it is important to store this context [the timezone in effect] with the time: It may not seem important, but if you don't store this information, you haven't stored a timestamp, you've stored a meaningless number. This is espacially true when you have to compare timestamps for different systems (that may or may not be in the same time zone).

Now we get to the rant bit: Oracle doesn't let you do this. While its time/date handling may be considered to admirable, flexible and powerful in many other ways, it is, at the end of the day, meaningless.

For example, if you have a PL/SQL procedure that modifies a row in a table and stores a timestamp in a field somewhere, you cannot later come back and look at the contents of that field, and know what the time actually was.

I've read documentation that says that SYSDATE() [for example] returns the time in the Oracle client's timezone. And I've read documentation that claims that it returns the time in the server's timezone. Whichever one of these it is is unimportant. The important bit is that although Oracle uses this timezone, it won't tell you what it actually is and doesn't store this information in its date datatype.

And it has, as far as I can tell, no way of providing you with a time in UTC, or any specific timezone: The best it can do is cast times between two user supplied timezones [of which it knows only 5 or 6 - The American timezones and UTC] which is only useful if you know what timezone Oracle is supplying times in, which it won't tell you...

Utter Braindeath⁰.

However, all is not quite lost: You can compile C extensions to PL/SQL, which allow you to get the necessary information. Since I've already gone so far as to raise my blood pressure and your boredom level, I might as well provide the solution I came up with: There's probably a better way, but this is the one I came up with.

• Licence: The GPL
• Makefile
• ora_time.c
• ora_strcasecmp.c ¹
• ora_url.c ²
• reload_library.pl Makefile helper script
• PL/SQL Package
• PL/SQL Usage Examples