Generating a sequential date series in Oracle

2009-02-11 19:00:00 -0500


I needed a bit of SQL to gin up a series of sequential dates in Oracle that I could use in generating data for flash charts in APEX. It’s important to have a solid series of dates that you can LEFT OUTER JOIN against so that NULL values correspond to one of your series points, keeping all the data properly aligned.

In looking around for some good examples I found some way more complex date series tricks
, but didn’t see any article on the simple basics of doing a series of dates between a start and end date. Here’s what I found along the way:


-- notes for generating date sequences in oracle
--
-- gleaned from a pretty interesting article by Indrajit Agasti on generating
-- various series of working (non-weekend) days:
-- http://www.dba-oracle.com/t_test_data_date_generation_sql.htm
--
-- i'm sure this old hat for many oracle devs but i didn't see it out there
-- anywhere so i figured a quick ref of notes i took in the process might
-- be useful to somebody else

-- to knock a string into a date object:
SELECT TO_DATE('01-JUN-2008') FROM DUAL;

-- getting an integer difference between two dates:
SELECT TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008') FROM DUAL;

-- generating a sequential series using dim (this is odd, admittedly)
-- 0, 1, 2 ... 9 (n - 1)
SELECT dim FROM DUAL
MODEL
DIMENSION BY (0 dim) MEASURES (0 rnum)
RULES ITERATE (10) (
rnum [ITERATION_NUMBER] = ITERATION_NUMBER
);

-- easier, returns 1,1,1... (10 rows), so use rownum to get increasing integers
SELECT ROWNUM FROM (
SELECT 1 FROM DUAL
CONNECT BY LEVEL <= 10
);

-- drop in our dates
-- returns 1, 2, 3 ... 31
SELECT ROWNUM FROM (
SELECT 1 FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008'))
);

-- full date set then: (may 1st, to may 31st)
SELECT TO_DATE('01-MAY-2008') + ROWNUM - 1
FROM (
SELECT ROWNUM FROM (
SELECT 1 FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008'))
)
);

-- being inclusive of the end-date (june 1st)
SELECT ROWNUM FROM (
SELECT 1 FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008') + 1)
)

Palm Strip: Fork and be Merry

2009-02-10 19:00:00 -0500


We’ve mentioned in the past that we’re no longer supporting or developing Strip on the PalmOS platform. Even so, people still ask about looking at the code and making updates. Strip for Palm OS is free and open-source software, so we’ve pushed the code up to Github to facilitate any further development others may wish to do. It’s the beauty of open source – feel free to fork it, hack it, send pull requests, or release your own version. Fair warning – the code was written a long time ago on an ancient (in relative terms) platform so it might leave something to desired if you’re used to more modern toolkits!


The State of MySQL: The Elephant in the Room

2009-02-09 19:00:00 -0500


It’s been in the news for a few days: two of the MySQL execs, Martin Mickos and Monty Widenus, are leaving Sun Microsystems, Sun having bought and absorbed MySQL AB.

I’ve always been a Sun fan (Solaris FTW), but it’s a well-known joke that being bought-out by Sun is the kiss of death. So what happens to MySQL now, and what happens in the open-source database space? Drizzle, an open-source and derivative project led by Brian Aker, doesn’t seem to have clearly defined its space (“the cloud?”), with people wondering if it’s really just a SQLite competitor (alternative might be a better turn of phrase because it’s got a long way to go before it could “compete” with SQLite). Speculation aside, what is known is that it drops many of the features that make MySQL a full-featured relational database.

Ladies and gentlemen, I think that we have a winner, the elephant in the room: PostgreSQL. Somebody’s gotta say it. Sorry to gloat. PostgreSQL is and has been “the most advanced open-source database,” long before these MySQL upstarts came along.

In the end, MySQL was never a completely open project. MySQL AB offered a GPL version but only included code that they owned the copyright to. Contributing developers wouldn’t see changes included in the core unless they were rewritten by MySQL AB or donated (read sign over their IP) to the company. It’s pretty heinous to tout such a model as an open-source success story, and it looks like the opposite is now true: MySQL is clearly floundering.


Strip: News from the Workbench

2009-02-09 19:00:00 -0500


It’s a been a little while since I’ve given any kind of update on our progress with STRIP for the iPhone platform. We’ve been hammering away at our data model and at this point I think we’ve arrived at a fairly stable first implementation and work-flow for the app. After changing it what seems a billion times, I think we’ve got it. For now.

There are still some tweaks we need to make before we start the beta of the initial version. Also of note: wireless backup and sync won’t be available in the initial version. It will be done, oh yes, and it will be done well, but not in time for the first release. We want to at least get this out there for Strip fans to test it out and make sure we’re going in the right direction here. This feature is really important to us, as it’s one of the features that really made the previous versions of Strip stand out, and we’re committed to its implementation.

We’ve put together a mailing list just for Strip (low volume, announcement only) for those of you who want to be alerted directly when we’re ready to do the beta. Sign up for it over on the Strip page.

We still need to put together an export tool for old Palm OS Strip users who want to migrate to various systems, and a means to import that export into the iPhone version. We intend to have that piece in place by the end of the beta, before we go live, but I can’t commit to a time-frame just yet.

Thanks again for your patience as we work hard to get this right.


First Takes

2009-02-05 19:00:00 -0500


On the 37Signals blog today there was some talk about Axl Rose and Frank Sinatra, two musicians I love to gab about:

Sinatra’s one take style produced classics. Axl’s dithering produced a pile of mush. We can all learn something from that. It’s easy to fall into a trap of nitpicking over things that don’t really matter. Instead, focus on the essence of what you’re doing. Press record, get it done, and get it out there.

Lately I’ve taken our first takes on demos for the new band, or just scrapping them and coming back to it when the time is right. Sometimes that means a different approach, sometimes a better room sound. I almost always go for a first take on a guitar solo, if I don’t double it with another one for maximum shred \m/.

With that in mind, an update on our progress with Strip should be forthcoming.