Home » SQL & PL/SQL » SQL & PL/SQL » 30 minute Time Interval
( ) 1 Vote
|
Re: 30 minute Time Interval [message #611005 is a reply to message #611004] |
Wed, 26 March 2014 13:46   |
John Watson
Messages: 8977 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: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #611015 is a reply to message #611013] |
Wed, 26 March 2014 14:54   |
Solomon Yakobson
Messages: 3305 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.
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 31 20:01:01 CDT 2025
|