Ordinary Oracle Joe

Just an ordinary DBA's thoughts

Just for fun – weekends til the end of the year

Posted by oakesgr on April 28, 2010

Someone asked me to create a list of dates for all Saturdays and Sundays until the end of the year. Just for fun I asked one of the other dbas to have a go at it too. Here’s the different answers we came up with along with the attached explain plans…

My first attempt after a quick bit of googling

SELECT TO_CHAR (DAY, 'DD-MON-YYYY')
FROM (SELECT SYSDATE - 1 + ROWNUM AS DAY
FROM all_objects
WHERE SYSDATE - 1 + ROWNUM <= TO_DATE ('31/12/2010', 'DD/MM/YYYY'))
WHERE TO_CHAR (DAY, 'D') IN (7, 1)
/

However, I wasn’t particularly happy with this as you have to query all_objects (or some other table) and in the back of my mind I remembered some trick with ‘connect by level’ that I’d seen before to create a list of integers. Google is my friend and I found this on asktom from Mikito Harakiri which I shamelessly copied, hacked and ended up with the following query

SELECT TO_CHAR (SYSDATE + num, 'DD-MON-YYYY')
FROM (SELECT LEVEL num
FROM DUAL
CONNECT BY LEVEL <= 365)
WHERE TO_CHAR (SYSDATE + num, 'D') IN (7, 1)
AND SYSDATE + num <= TO_DATE ('31/12/2010', 'DD/MM/YYYY')
/

In the meantime my dba friend had come up with…

SELECT TRUNC (SYSDATE + a) AS "Saturday", TRUNC (SYSDATE + a) + 1 AS "Sunday"
FROM (SELECT LEVEL a
FROM DUAL
CONNECT BY LEVEL <= 365)
WHERE TO_CHAR (SYSDATE + a, 'D') = 7
AND EXTRACT (YEAR FROM SYSDATE) = EXTRACT (YEAR FROM SYSDATE + a)
/

So it seems that the CONNECT BY LEVEL trick is the way to generate the list of dates, at least that’s the best we found. The filtering functionality can be done in a myriad of ways.

If you’re really interested all the associated explain plans are here. You’ll notice that the plan for the first attempt looks horribly complex compared to the later attempts. However, this is simply a function of using the all_objects view to create the integers to add to sysdate.

Did we miss an easier way? Answers on a postcard to…

Advertisements

4 Responses to “Just for fun – weekends til the end of the year”

  1. mwidlake said

    How about:

    select cal.cal_date
    from calendar cal
    where to_number(to_char(cal.cal_date,’D’) between 6 and 7
    and cal.cal_date between sysdate
    and to_date (’31-DEC-2010′,’DD-MON-YYYY’)
    /
    {nb D starts with 1 for Monday
    1* select sysdate, to_char(sysdate,’D’) ,TO_CHAR(SYSDATE,’DAY’) from dual
    SYSDATE T TO_CHAR(SYSDATE,’DAY’)
    —————– – —————————
    28-APR-2010 23:15 3 WEDNESDAY}

    That should work.

    What was that? You do not have a CALENDAR table in your schema? Why in heck not? we always had one back in the 90’s! It was really useful, like that table you created to hold one row to list the details of you system (name, version, last_patch_applied, system owner, name_of_system,help_system_level etc…)

    *sigh*

    AM I just getting too old?

    OK, I’ll have a think about how to get the weekends when you have no calendar table, and I think you have it spot-on Graham that you need the CONNECT BY LEVEL trick to make it “efficient”. I think something like:

    select to_char(sysdate+rownum,’D’)

    CONNECT BY LEVEL (365-to_char(sysdate,’DDD’))

    would be most efficient, as it would only consider the number of days left for the year.

    You do realise that if you have to tune this one-off piece of code to get the weekends then you are still running your database on one of Doug’s old Spectrums, don’t you?

    • oakesgr said

      Are you REALLY asking me if you’re getting too old? 🙂 LOL.

      Seriously though, how many of those ‘old’ tricks, techniques etc. have we forgotten about these days? shocking! When I was young….

      I like your addition so that it only considers the days we need though. I’m sure Doug could run it quicker with his Speccy, but when you’ve only got 48k to play with you need to be tight with your code!

  2. Boneist said

    My nls independent version, although sadly without a calendar table:


    with mt as (select trunc(sysdate) -1 + level dt
    from dual
    connect by level <= add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate))
    select dt,
    to_char(dt, 'Dy', 'nls_date_language=english') dy
    from mt
    where to_char(dt, 'Dy', 'nls_date_language=english') in ('Sat', 'Sun');

    • oakesgr said

      Boneist, I knew that if anyone would like to perform a little query tuning / rewriting it would be you! like a moth to a flame 🙂

      I’m loving the NLS independence, it was bothering me a little bit that I couldn’t run my query the next time I’m in Saudi!

      It just goes to show how much we can make of even the simplest requirements when we really want to. What else can we add in? LOL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: