Saturday, February 11, 2012

How To: Convert an Oracle FLOAT Value to A Time Span [Field Notes]

The Problem
We have a bunch of "durations" stored in Oracle as FLOAT datatypes (as day intervals, meaning they are an interval that includes days).

This makes sense. from a database storage perspective. However, I didn't know how to get those values into the more human-comprehensible hh:mm:ss format.

The Solution
An Oracle function called NUMTODSINTERVAL is exactly what we need here. As the name suggests, it converts a number to a DateStamp interval (which is to say, interprets a number as a time span). We can tell it the type of time span (days in this case), and then extract the appropriate time values.

Syntax: NUMTODSINTERVAL ([yourvalue], '[typeofinterval]')
Basic Example: NUMTODSINTERVAL (tablename.durationfield, 'DAY')
The above example takes my field named 'durationfield' and tells oracle it's a DAY interval.

From here, we use the EXTRACT function to get what we want.

Solution Examples
Gets the number of hours (and converts all day values to 24 hours each):
EXTRACT(DAY FROM NUMTODSINTERVAL(TABLENAME.DURATIONFIELD, 'DAY')) * 24
+ EXTRACT(HOUR FROM NUMTODSINTERVAL( TABLENAME.DURATIONFIELD , 'DAY')) 
AS DURATIONHOURS

Gets the minutes in the same fashion:
EXTRACT(MINUTE FROM NUMTODSINTERVAL( TABLENAME.DURATIONFIELD , 'DAY'))                                                                   
AS DURATIONMINUTES

Gets the seconds in the same fashion:
EXTRACT(SECOND FROM NUMTODSINTERVAL( TABLENAME.DURATIONFIELD, 'DAY'))                                                                  
AS DURATIONSECONDS

Tuesday, February 07, 2012

SQL Tricks: The Not-Equals Join [Field Notes]

The Problem
I needed to pull back a list of dates in a certain range for each employee, but didn't have a common join criteria, as I wanted all the dates but they did not come from a table.

The Solution
I knew that the dates would never equal the employee name, so in this case, I could actually use a "not-equal" join to connect the two. I always forget about this one, but it's handy.

I did something along the following (edited for brevity):

select  * from (...) ActiveLabor left outer join (...)DateRange on (activelabor.laborcode != to_char(DateRange.DateItem))

This allows the left outer join only if the items are not equal (which we happen to know will always be the case).

The end result? As I expected -- the full list of dates showed up for each employee, just as I'd hoped.

A Note: Because I was comparing a date to a string, I had to cast the date as a varchar2 in the join statement. The date value itself still comes back as a date, but during the comparison we need to make sure we're giving Oracle an apples-to-apples comparison.

Monday, February 06, 2012

February 6, 2012 [The Grocery List]

Stopped by Giant and picked up:

  • Baby bella mushrooms
  • Peppers (assorted red, green, yellow)
  • 2 Peaches
  • 6 apples
  • A spaghetti squash (god, I love that spaghetti squash)
  • Spinach

How To: List all Dates for sysdate +/- 30 in Oracle

Discovered an answer to a StackOverflow question I'd posted, and wanted to share.

I needed to, within a view, dynamically generate a list of dates before or after sysdate. I wanted to do this so that I could limit some of the records coming back (we have millions but the date range will always be between 31 days before sysdate or 31 says after.

In Oracle, the SQL to do this is as follows:

SELECT *
FROM   (SELECT TRUNC(SYSDATE - ROWNUM) DateItem
        FROM   DUAL
        CONNECT BY ROWNUM < 32
        UNION
        SELECT TRUNC(SYSDATE + ROWNUM) DateItem
        FROM   DUAL
        CONNECT BY ROWNUM < 32)DATERANGE; 
This SQL does the following:

  • Uses the dual table (a special table in Oracle as I understand) to scroll through x amount of rows (in this case, 32, because we want to make sure it includes 31 days)
  • For each of those, it returns the sysdate - the number of rows, or sysdate + the number of rows in the case of the second query. It calls this column "DT".
  • It unions those two queries together, to give the full range of values. This also has the nice effect of removing duplicate dates from the results.
  • I then wrap it in a select * statement so I can call it what I want and refer to it later.
Learning Oracle as I go, and I'm enjoying discovering these little tricks.