OraFAQ Forum: SQL & PL/SQL » 30 minute Time Interval

Home » SQL & PL/SQL » SQL & PL/SQL » 30 minute Time Interval (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: 30 minute Time Interval [message #611005 is a reply to message #611004]

Wed, 26 March 2014 13:46

John Watson
Messages: 8978
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 #611006 is a reply to message #611004]

Wed, 26 March 2014 13:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>I am using Oracle pl/sql
SQL & PL/SQL are entirely different languages.
SELECT statement is plain SQL.

what datatype is column "DT"?

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

Code: [Select all] [Show/ hide]

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):

Code: [Select all] [Show/ hide]

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 #611009 is a reply to message #611008]

Wed, 26 March 2014 13:58

ertweety11
Messages: 9
Registered: March 2014

Junior Member

I tried your code but I got ORA-00932: inconsistent datatype. Expected number got DATE. Seems like it didn't like the first line.

Report message to a moderator

Re: 30 minute Time Interval [message #611010 is a reply to message #611009]

Wed, 26 March 2014 13:59

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And I tried my code I have not the same error:

Code: [Select all] [Show/ hide]

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 [table] 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  /
from [table] o
     *
ERROR at line 3:
ORA-00903: invalid table name

Report message to a moderator

Re: 30 minute Time Interval [message #611011 is a reply to message #611010]

Wed, 26 March 2014 14:01

ertweety11
Messages: 9
Registered: March 2014

Junior Member

strange...wonder why it isn't working for me.

Report message to a moderator

Re: 30 minute Time Interval [message #611012 is a reply to message #611009]

Wed, 26 March 2014 14:01

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ertweety11 wrote on Wed, 26 March 2014 19:58

I tried your code but I got ORA-00932: inconsistent datatype. Expected number got DATE. Seems like it didn't like the first line.

Note I have modified my query to:

Code: [Select all] [Show/ hide]

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
/

but still not tested until you post a test case.

Report message to a moderator

Re: 30 minute Time Interval [message #611013 is a reply to message #611011]

Wed, 26 March 2014 14:03

ertweety11
Messages: 9
Registered: March 2014

Junior Member

You are awesome! Thank you so much. It worked Smile

Smile

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: 8978
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:

Code: [Select all] [Show/ hide]

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?

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:03

You are awesome! Thank you so much. It worked

Really? There is a little wrinkle in Michel's code - missing trunc:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: 30 minute Time Interval [message #611016 is a reply to message #611015]

Wed, 26 March 2014 14:57

ertweety11
Messages: 9
Registered: March 2014

Junior Member

Yes, I figured that out when I ran it. Was able to adjust it and make it work. Thanks!

Report message to a moderator

Re: 30 minute Time Interval [message #611017 is a reply to message #611015]

Wed, 26 March 2014 15:02

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

As you can see, it didn't return what you expected. As I already mentioned, GROUP BY is missing TRUNC:

Yes this is why I always ask for a test case; the error is obvious and it could be fixed at the first execution.

Report message to a moderator

Re: 30 minute Time Interval [message #611018 is a reply to message #611016]

Wed, 26 March 2014 15:03

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ertweety11 wrote on Wed, 26 March 2014 20:57

Yes, 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?

Report message to a moderator

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:57
Yes, 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.

Report message to a moderator

Re: 30 minute Time Interval [message #611144 is a reply to message #611142]

Fri, 28 March 2014 07:04

ertweety11
Messages: 9
Registered: March 2014

Junior Member

correct solution

Code: [Select all] [Show/ hide]

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 trunc(to_number(to_char(o.dt,'SSSSS'))/1800)
ORDER BY trunc(to_number(to_char(o.dt,'SSSSS'))/1800);

Report message to a moderator

Re: 30 minute Time Interval [message #611245 is a reply to message #611144]

Mon, 31 March 2014 05:37

oralover2006
Messages: 144
Registered: January 2010
Location: India

Senior Member

thanks very much for your feedback Smile

Smile

Report message to a moderator

Previous Topic:	Case statement with null in where clause
Next Topic:	Longest word PL SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 16 06:32:06 CDT 2025