Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
query for all hours in a month?

764672
Member Posts: 3
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
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
-
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). -
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
-
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 WorksSELECT 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 -
Hi,
Wel-come to the forum.
This might helpSELECT (&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 -
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.