| 30 minute Time Interval [message #611004] |
Wed, 26 March 2014 13:39  |
 |
ertweety11
Messages: 9
Registered: March 2014
|
Junior Member |
|
|
I am using Oracle pl/sql and I am trying to do a count of memberships by 30 minute time intervals between the hours of 9:00 and 16:00.
Here is the code I have so far;
select to_char(o.dt,'hh24:mi') as hr_dy, count (o.membership_num) as tl
from [table] o
where o.dt >= '2014-03-25' and o.dt <'2014-03-26'
GROUP BY to_char(o.dt,'hh24:mi')
Don't know how to count the 9:24 time in the first 30 minute interval and 9:36 in the second.
Want data to show as follows;
9:00 total (this would include total from 9:00-9:29)
9:30 total
10:00 total
etc
|
|
|
|
| Re: 30 minute Time Interval [message #611005 is a reply to message #611004] |
Wed, 26 March 2014 13:46  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
In your question, Quote:a count of memberships by 30 minute time intervals between the hours of 9:00 and 16:00 you are missing one vital bit of information: between 09:00 and 16:00, but of which day? If you answer that, a solution may be possible.
And, as a general principle, you must always use type casting when comparing dates to strings. You are not doing that in your predicate.
--update:
sorry, I mis-read your question: you have specified the day. So I think your problem is to do with slicing hours in two? (But my comment regarding type casting is still relevant.
[Updated on: Wed, 26 March 2014 13:51] Report message to a moderator |
|
|
|
|
|
| Re: 30 minute Time Interval [message #611007 is a reply to message #611005] |
Wed, 26 March 2014 13:50 |
|
ertweety11
Messages: 9
Registered: March 2014
|
Junior Member |
|
|
Sorry, new to the forum. Just added code tags. In regards to the day, it does show my date as being March 25th.
An example of the DT field in my data is 1/19/2012 2:10:00 AM
select to_char(o.dt,'hh24:mi') as hr_dy, count (o.membership_num) as tl
from [table] o
where o.dt >= '2014-03-25' and o.dt <'2014-03-26'
GROUP BY to_char(o.dt,'hh24:mi')
[Updated on: Wed, 26 March 2014 13:52] Report message to a moderator |
|
|
|
| Re: 30 minute Time Interval [message #611008 is a reply to message #611004] |
Wed, 26 March 2014 13:54 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Something like (of course not tested as we have no test case to test with):
select to_char(to_date(trunc(to_number(to_char(o.dt,'SSSSS'))/1800)*1800,'SSSSS'),'HH24:MI') as half_hr,
count (o.membership_num) as tl
from [table] o
where o.dt >= DATE '2014-03-25' and o.dt < DATE '2014-03-26'
GROUP BY to_number(to_char(o.dt,'SSSSS'))/1800
/
[Updated on: Wed, 26 March 2014 13:59] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: 30 minute Time Interval [message #611014 is a reply to message #611007] |
Wed, 26 March 2014 14:08 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, I hope you saw my correction above. Thank you for the [code] tags.
The group by clause you have will group by minute, but you need to group by 30 minutes. Look at this:
select trunc(systimestamp,'hh24'),
extract(minute from systimestamp),
trunc(systimestamp,'hh24')+(case when extract(minute from systimestamp)<=30 then 0 when extract(minute from systimestamp)>30 then 30 end) from dual;
It is not pretty, but I think if you group by the last expression, it should work?
|
|
|
|
| Re: 30 minute Time Interval [message #611015 is a reply to message #611013] |
Wed, 26 March 2014 14:54 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ertweety11 wrote on Wed, 26 March 2014 15:03You are awesome! Thank you so much. It worked 
Really? There is a little wrinkle in Michel's code - missing trunc:
SQL> select *
2 from o
3 /
DT MEMBERSHIP_NUM
------------------- --------------
03/25/2014 00:29:00 1
03/25/2014 00:30:00 1
03/25/2014 00:31:00 1
03/25/2014 00:32:00 1
SQL> select to_char(to_date(trunc(to_number(to_char(o.dt,'SSSSS'))/1800)*1800,'SSSSS'),'HH24:MI') as
half_hr,
2 count (o.membership_num) as tl
3 from o
4 where o.dt >= DATE '2014-03-25' and o.dt < DATE '2014-03-26'
5 GROUP BY to_number(to_char(o.dt,'SSSSS'))/1800
6 ORDER BY trunc(to_number(to_char(o.dt,'SSSSS'))/1800)
7 /
HALF_ TL
----- ----------
00:00 1
00:30 1
00:30 1
00:30 1
As you can see, it didn't return what you expected. As I already mentioned, GROUP BY is missing TRUNC:
SQL> select to_char(to_date(trunc(to_number(to_char(o.dt,'SSSSS'))/1800)*1800,'SSSSS'),'HH24:MI') as
half_hr,
2 count (o.membership_num) as tl
3 from o
4 where o.dt >= DATE '2014-03-25' and o.dt < DATE '2014-03-26'
5 GROUP BY trunc(to_number(to_char(o.dt,'SSSSS'))/1800)
6 ORDER BY trunc(to_number(to_char(o.dt,'SSSSS'))/1800)
7 /
HALF_ TL
----- ----------
00:00 1
00:30 3
SY.
|
|
|
|
|
|
|
|
|
|
| Re: 30 minute Time Interval [message #611142 is a reply to message #611018] |
Fri, 28 March 2014 06:54 |
oralover2006
Messages: 144
Registered: January 2010
Location: India
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 27 March 2014 01:33
ertweety11 wrote on Wed, 26 March 2014 20:57Yes, I figured that out when I ran it. Was able to adjust it and make it work. Thanks!
And why don't you post the correct solution instead of leaving the topic with an incorrect one?
dear ertweety11, in this way you can help others like GURUS helped you/us. show your attitude.
regards.
|
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
