Forum Stats

  • 3,768,631 Users
  • 2,252,824 Discussions
  • 7,874,658 Comments

Discussions

query for all hours in a month?

764672
764672 Member Posts: 3
edited Apr 5, 2010 4:58PM in SQL & PL/SQL
Hello,

I was hoping someone might be able to assist. I need to query for all the hours in a user specified date range (generally a month). For example,
a query such as:
select all hr_end from dual where start_date between '01-feb-2010' and '28-feb-2010'

that returns:

feb 01 2010 01:00
feb 01 2010 02:00
feb 01 2010 03:00
...
feb 28 2010 23:00
feb 28 2010 24:00

Ideally, I want to be able to run this query within a WITH clause such that I can reference the temporary table/ list of hours in a subsequent select statement quickly. Something like:

With
all_hrs_in_month AS
select .....
where start_date between '01-mar-2010' and '31-mar-2010'

I've looked around for similar questions, but have only found ones for all the 'days' in a month using level, connect by, row_num, for which I'm honestly not too familiar with and wasn't clear as to how I could modify to my needs.

Greatly appreciate your help with this request.

- j

Edited by: user12942939 on Apr 5, 2010 12:04 PM

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Apr 5, 2010 3:01PM
    Well, this should give you every hour between your start_date and end_date
    select 
      start_date + (1/24 * (level-1)) 
    from 
    (
      select    
        to_date('01-mar-2010', 'dd-mon-yyyy') as start_date,
        to_date('31-mar-2010', 'dd-mon-yyyy') as end_date
      from dual
    ) 
    connect by level <= (end_date - start_date ) * 24;
    And then you'd have to slap that in to your existing query (and outer join to it if you needed data for every hour in the time span).
    Tubby
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    I've taken that CONNECT BY level concept and changed the units from days to hours.

    Hope this helps!
    WITH parameters AS
    (
            SELECT  TO_DATE('03/01/2010','MM/DD/YYYY') AS START_DAY
            ,       TO_DATE('03/31/2010','MM/DD/YYYY') AS END_DAY
            FROM    DUAL
    ), hour_range AS
    (
            SELECT  START_DAY + (LEVEL-1)/24 AS DT
            FROM    PARAMETERS
            CONNECT BY LEVEL <= (END_DAY - START_DAY + 1)*24 
    )
    SELECT * FROM HOUR_RANGE
    ORDER BY 1
    Centinul
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,210 Red Diamond
    edited Apr 5, 2010 3:27PM
    Hi,

    Welcome to the forum!
    WITH	got_parameters  AS
    (
    	SELECT	TO_DATE ('01-mar-2010 00:00', 'dd-mon-yyyy hh24:mi')	AS start_date
    	,	TO_DATE ('31-mar-2010 23:00', 'dd-mon-yyyy hh24:mi')	AS end_date
    	FROM	dual
    )
    ,	all_hrs		AS
    (
    	SELECT	start_date + ( (LEVEL - 1)
    			     / 24
    			     )	AS hr
    	FROM    got_parameters
    	CONNECT BY	LEVEL <= 1 + ( 24
    			      	     * (end_date - start_date)
    				     )
    )
    SELECT	  TO_CHAR (hr, 'DD-Mon-YYYY HH24:MI')	AS h
    FROM	  all_hrs
    ORDER BY  hr
    ;
    Output:
    H
    -----------------
    01-Mar-2010 00:00
    01-Mar-2010 01:00
    01-Mar-2010 02:00
    01-Mar-2010 03:00
    ...
    31-Mar-2010 22:00
    31-Mar-2010 23:00
    Notice that start_date and end_date don't have to span then entire month; they don't even have to be in the same month.
    If you'd rather specify just one parameter (such as a single string containing the month and year):
    WITH	got_parameters  AS
    (
    	SELECT	TRUNC ( TO_DATE (:p_month, 'mon-yyyy')
    		      , 'MONTH'
    		      )		AS start_date
    	,	TRUNC ( ADD_MONTHS ( TO_DATE (:p_month, 'mon-yyyy')
    		      		   , 1
    				   )
    		      , 'MONTH'
    		      )		AS end_date
    	FROM	dual
    )
    ,	all_hrs		AS
    (
    	SELECT	start_date + ( (LEVEL - 1)
    			     / 24
    			     )	AS hr
    	FROM    got_parameters
    	CONNECT BY	LEVEL <= ( 24		-- NOTE: Not adding 1 here
    			      	 * (end_date - start_date)
    				 )
    )
    SELECT	  TO_CHAR (hr, 'DD-Mon-YYYY HH24:MI')	AS h
    FROM	  all_hrs
    ORDER BY  hr
    ;
    How It Works
    SELECT  LEVEL  AS n
    FROM    dual
    CONNECT BY  LEVEL <= x
    ;
    Produces a result set consiting of the integers 1, 2, 3, ..., x.
    There's nothing magical about the dual table; you can use any table AS LONG AS THE TABLE HAS ONLY ONE ROW .
    The other examples you saw probably just added this to a starting date, to get successive days, since, in Oracle date arithmetic, dt+n is a DATE n days after dt.
    Your case is slightly more complicated, because you want to add hours, not days. Since an hour is 1/24 of a day, we multiply by 24 to find how many integers to genereate, and divide by 24 when adding that number to the base date.

    Edited by: Frank Kulash on Apr 5, 2010 3:07 PM
    Frank Kulash
  • 728534
    728534 Member Posts: 1,386
    Hi,
    Wel-come to the forum.

    This might help
    SELECT (&START_DATE + (LEVEL - 1) / 24) all_hours_month
      FROM DUAL
    CONNECT BY LEVEL <= ((&END_DATE+1 - &START_DATE) * 24)+1
    Remember 28-feb-2010 24:00 is same as 01-mar-2010 00:00

    Cheers!!!
    Bhushan
    728534
  • 764672
    764672 Member Posts: 3
    Thanks all for your help!!

    I was pretty surprised by the speed and completeness of everyone's responses. Really appreciate the help,

    j
This discussion has been closed.