Converting Time Zones in Oracle

In my previous article Oracle, SQL, Dates and Timestamps I talked about inserting, selecting and comparing dates in Oracle. Now I want to cover some functionality for converting between time zones.

We’ll use the table in the form specified in the previous article, but insert one more row:

insert into dates values(6, to_date('09/20/05 23:15', 'MM/DD/YY HH24:MI'));

The contents of the table now look like this:

1 09/14/05, 21:08
2 09/27/05, 00:00
3 10/02/05, 22:05
4 09/01/05, 17:01
5 09/12/05, 14:30
6 09/20/05, 23:15

Changing Time Zones

The date format in Oracle does not contain time zone information, but the database does. To find out the time zone set, execute this query:

SELECT dbtimezone FROM dual;


The time zone can be updated with the command:

ALTER database SET TIME_ZONE = '-05:00';

where you can specify the offset from Greenwich mean time or a valid time zone from the list in the v$timezone_names view. Note that this is one of the few of the ‘v$’ views which are plural.

Switching Time Zones

The function new_time is used to convert a time to different time zones. To illustrate this we’ll look at entry 5 from the dates file.

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 02:30 PM

This database is in US Eastern time but we want to display the time in US Central.

SELECT entry, to_char(new_time(entry_date, 'EST', 'CST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 01:30 PM

Here we clearly see the time converted to Central. Note that the new_time function is performed on the date field, not on the to_char. Now let’s grab this time in Pacific time:

SELECT entry, to_char(new_time(entry_date, 'EST', 'PST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 11:30 AM

Now we see not only the time converted, but also the time of day has gone from PM to AM.

Now let’s take a look at entry 6:

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/20/05 11:15 PM

We’ll again assume this timestamp is in US Eastern time, but let’s convert it this time to Greenwich Mean Time.

SELECT entry, to_char(new_time(entry_date, 'EST', 'GMT'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/21/05 04:15 AM

This shows not only the change in hours, but that the date of this entry is displayed properly for its time zone.

Of course the new_time function can be used on inserts in the same way. This is useful if you are allowing input from people in different geographical regions. Here we convert an entry made in Pacific Time to Eastern:

new_time(to_date('09/22/05 10:28 AM', 'MM/DD/YY HH:MI AM'), 'PST', 'EST'));

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=7;

7 09/22/05 01:28 PM

So we have converted 10:28 AM Pacific to 1:28 PM Eastern so all our entries in the table are consistent. Of course when performing the insert we need to put the to_date function within the new_time function so the text string is converted to a date format before we try to convert it.

oracle, sql, dba, database administration, database development

SQL Join Syntax

I have always found the different types of joins a bit confusing, but now thanks to a little experimenting I think I have a handle on it. This is geared toward Oracle, but most of this is ANSI SQL, so should work in other databases as well. Some of these features may not be available in Oracle pre-9i.

Assume these two tables:

SELECT * FROM faculty;

1 Jon Emmons
2 Zach Tirrell
3 Evelyn Stiller

SELECT * FROM class;

1 CS3600 1
2 CS3020 2
3 CS2000  
4 CS1100 1

Inner Join

The most common (and simple) join. This will select the rows which satisfy the join condition; however if a row exists in one table but does not have a counterpart to fulfill the join condition those rows (from either table) will be ignored.

FROM faculty, class
WHERE = class.instructor_id;

1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
1 Jon Emmons 4 CS1100 1

Here we see the three rows where had a match to class.instructor_id.

Left Join

A left join, a.k.a. left outer join will return all the row combinations which meet the join condition plus any rows from the first table which do not meet the guard condition.

FROM faculty LEFT JOIN class

1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      

Now we see all the results we saw in the inner join, but we additionally see the row from faculty (id 3, Evelyn Stiller) which has no corresponding row in class.

Right Join

Also referred to as right outer join, this will show all row combinations which meet the join criteria, but will additionally show any rows from the second table which do not have counterparts in the first.

FROM faculty RIGHT JOIN class

1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
      3 CS2000  
1 Jon Emmons 4 CS1100 1

Now we see rows from the class table which do not have a faculty counterpart.

Outer Join

The outer join, or full outer join can be thought of a left join and right join. Rows which meet the join condition will of course be displayed, additionally, rows from both the first and second table referenced will be displayed.

FROM faculty FULL OUTER JOIN class
ON = class.instructor_id;

1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      
      3 CS2000  

Now we see all the rows from both the tables, joined where the join condition is met, or with corresponding null values where the join condition failed.

oracle, sql, dba, database administration, database development

The Straight Poop on Kopi Luwak Coffee

palm_civetAll about this infamous coffee and the cat-like creature that makes it possible.

Years ago I heard a rumor of a rare coffee that was collected from the droppings of a wild animal. For quite a while, having nothing to substantiate this I had dismissed this as either an urban legend, or something so rare I was unlikely to ever have access to it.

Well thanks to the coffee explosion this rare and unusual blend is readily available, though still very expensive. My interest in this was rekindled when I noticed Armeno Coffee Roasters, my favorite source for fine coffee is now carrying Kopi Luwak Coffee.

So what exactly is it? The Indonesian word “kopi” translates directly to coffee. The word Luwak refers to a small wild animal native to Indonesia and Vietnam.

So you know what coffee is… what’s this luwak critter? Known as the luwak, luak, musang, toddy cat, civet, palm civet and civet-cat, many people believe it is a wild cat. While it is a mammal it is actually a cousin of the mongoose. Probably it’s closest North American counterpart is the skunk with which it shares the ability to excrete a noxious odor from scent glands near it’s anus. has some wonderful information on this animal.

The animal can range from four to eleven pounds and is largely nocturnal. While it is an omnivore, the luwak is particularly fond of perfectly ripe coffee cherries. Thanks to coffee farmers, the luwak has no troubles finding plenty of coffee.

Once eaten, the coffee cherries take the normal route through the animal’s digestive path. The amazing thing is while the fruit of the coffee is being digested, the bean is left largely unchanged, eventually passing in the animals droppings.

The droppings and their caffeine-laden content are collected by farmers. The coffee is then cleaned and the green, un-roasted bean shipped to roasters.

Why would you want to drink this shitty coffee? There are a lot of theories on why kopi luwak is different. Research has determined that coffee passed by a luwak has been changed chemically. Specifically the process seems to break down some of the bean’s proteins which are known to contribute to the bitterness of coffee.

So research from the University of Guelph in Canada, reported here, and further detailed in this article confirms the coffee’s chemical makeup is altered by it’s special little journey, I think it also important to consider the luwak’s own affinity for fine coffee.

When coffee, like other fruit, is harvested, not all the fruit will be perfectly ripe. Since the majority is ripe and it is to be combined for use, the end product comes out well; however, if you have an animal which naturally selecting only the ripest fruit you will end up with a product of unparalleled quality.

Is it safe? While many are understandably skeptical of eating anything that has already been through the gastro-intestinal track of another, research shows that due to the thorough washing, the luwak coffee may even have a lower bacteria level than other coffees. Certainly whatever the washing process misses the roasting process will make up for.

While I cannot say I have tried this rarest of all coffees, it is on my to-do list. Thankfully Armeno Coffee Roasters offers it in a four ounce sampler for a mere $30. Expect a detailed report here once the taste test is in.

An update: Thanks to Troy at I have now tried Kopi Luwak. Read my full review here.

NOAA Hurricane Katrina Images

NOAA image of Grand Isle, La., taken on Aug. 31, 2005, two days after Hurricane Katrina struck the U.S. Gulf Coast.The National Oceanic and Atmospheric Administration (NOAA) has posted many of their aerial images on their site.

In this article they outline much of what NOAA has been up to durring the cleanup process. It is interesting to see how involved they are, especially considering how little you hear about them. Don’t miss out on the “higher resolution version” links. They’re incredible!

Click here to view the full article from NOAA.
katrina, hurricane katrina, aerial photos, aerial images, weather, hurricane, louisiana, new orleans, storms

MAKE Volume 2: More Good Stuff

Make: Vol 2In the gaps between teaching, work, bloging and working on some pet-projects I have finally finished MAKE: Technology On Your Time, Volume 2. Chock full of home entertainment projects, I am yet again impressed with the quality and quantity.

Highlights include HDTV on Your Mac, Atari 2600PC, and instructions on how to build a twitchy little robot out of a computer mouse. I’m glad the magazine only comes out quarterly… I could spend three months working on the projects from just one issue!

While the quality of the projects outlined in MAKE is undeniable, I am finding the true value of the magazine is how it has changed the way I look at things. I can only equate this to when I learned how to pick locks. Once you have picked a Masterlock and a few door locks your attitude toward locked doors changes. What was once a barrier is now a challenge, even one to look forward to.

MAKE has changed the way I view the world. A broken answering machine, old mouse, dead entertainment system, even a cheap Commadore64 direct-to-TV game have taken on a new value. I’ve always been a hacker, never afraid to take the screws out and rewire, but this is going to a whole new level!

More than a book full of projects, MAKE is full of new ways to apply technology, new tools, and true “maker” attitude. The value of MAKE, and it’s true spirit, is realized when you view it not as a bunch of projects and reviews, but as 200 pages of raw information just waiting to see how the reader will put it all together.

Will I build a Lego robot controlled by an audio chip? Podcast a lecture for a class I will be teaching? Perhaps build a desktop linear accelerator to annoy and harm my cube-mates! (just kidding)

Well, one thing is for sure… I’m glad my girlfriend got me a subscription for my birthday. Time to dig into Volume 3. I’m afraid I’ve only got a couple months until Volume 4 will arrive.

For more information on what started this new obsession of mine, check out this article about MAKE, and this retrospective on Volume 1.

make, oreilly, make magazine, hacks, hack, hacker, electronics, electronic, entertainment, technology, electric, home entertainment, diy, do it yourself,