Home » SQL & PL/SQL » SQL & PL/SQL » Compare oracle timestamp (oracle 10g)
Compare oracle timestamp [message #336604] Mon, 28 July 2008 06:18 Go to next message
lelynx
Messages: 2
Registered: July 2008
Junior Member
Hi,
I have a set of data being stored in a timestamp datatype...

28-JUL-08 07.09.35.295000 AM
28-JUL-08 07.09.42.084000 AM
28-JUL-08 08.09.52.476000 AM
28-JUL-08 08.10.01.055000 AM
28-JUL-08 08.10.05.539000 AM
28-JUL-08 09.10.48.965000 AM
28-JUL-08 09.10.58.357000 AM
28-JUL-08 09.11.12.345000 AM
28-JUL-08 09.11.16.458000 AM
28-JUL-08 10.11.20.317000 AM
28-JUL-08 10.11.33.560000 AM
28-JUL-08 10.11.37.812000 AM
28-JUL-08 10.11.57.408000 AM

Now, i want a sql statement that can get me this kind of list
date -- time -- count
28-JUL-08 -- 7-8am -- 2
28-JUL-08 -- 8-9am -- 3
28-JUL-08 -- 9-10am -- 3
28-JUL-08 -- 10-11am -- 4

Well, i can use to_date function to compare the 'date' but how can i compare the 'time' so that i can get the 'count' for it?


My sql statement = SELECT date1 FROM xxx WHERE date1 >= to_date('27-JUL-2008','dd-MON-yyyy');
BTW, i'm using sqlplus

[Updated on: Mon, 28 July 2008 06:22]

Report message to a moderator

Re: Compare oracle timestamp [message #336606 is a reply to message #336604] Mon, 28 July 2008 06:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
have a look at the trunc function, and then do a group by the truncated date/time.
Re: Compare oracle timestamp [message #336613 is a reply to message #336606] Mon, 28 July 2008 06:45 Go to previous messageGo to next message
lelynx
Messages: 2
Registered: July 2008
Junior Member
sorry... a noob here...
how to trunc the time so that i can put it for my WHERE clause?
Re: Compare oracle timestamp [message #336628 is a reply to message #336613] Mon, 28 July 2008 07:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
sorry... a noob here...


So the number 1 priority is to figure out the online documentation. Wink

Further hint: In the example it's truncated to 'YEAR'. You want to truncate to hour so it's 'HH' instead of year.

And the way you explained what you want, yo don't want the trunc in the WHERE clause, you want it mainly in the GROUP BY clause (also follow the link to the examples)

Re: Compare oracle timestamp [message #336631 is a reply to message #336613] Mon, 28 July 2008 07:53 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

hi,
check this
SCOTT @ORCL> desc test2
 Name                                      	Null?    	Type
 ----------------------------------------- -------- ----------------------------
 A                                                  		TIMESTAMP(6)
SCOTT @ORCL> select * from test2
  2  /

A                                                                               
---------------------------------------------------------------------------     
28-JUL-08 07.09.35.295000 AM                                                    
28-JUL-08 07.09.42.084000 AM                                                    
28-JUL-08 08.09.52.476000 AM                                                    
28-JUL-08 08.10.01.055000 AM                                                    
28-JUL-08 08.10.05.539000 AM                                                    
28-JUL-08 09.10.48.965000 AM                                                    
28-JUL-08 09.10.58.357000 AM                                                    
28-JUL-08 09.11.12.345000 AM                                                    
28-JUL-08 09.11.16.458000 AM                                                    
28-JUL-08 10.11.20.317000 AM                                                    
28-JUL-08 10.11.33.560000 AM                                                    
28-JUL-08 10.11.37.812000 AM                                                    
28-JUL-08 10.11.57.408000 AM                                                    

13 rows selected.

SCOTT @ORCL> select to_char(a, 'DD-MON-YYYY') "Date", to_char(a, 'hh am') "Time", count(to_char(a, 'hh:mi:ss')) count
  2  from test2
  3  group by to_char(a, 'DD-MON-YYYY'), to_char(a, 'hh am')
  4  order by to_char(a, 'hh am')
  5  /

Date        Time       COUNT                                                    
----------- ----- ----------                                                    
28-JUL-2008 07 am          2                                                    
28-JUL-2008 08 am          3                                                    
28-JUL-2008 09 am          4                                                    
28-JUL-2008 10 am          4                                                    


hope it'll resolve your problem.
regards
Re: Compare oracle timestamp [message #336700 is a reply to message #336631] Mon, 28 July 2008 13:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, spoil a nice datatype (as well as good advice from ThomasG).
Let's continue your example.
Let's say we want to know the number of records having a timestamp > x

You cannot do that with your to_char. You will either have to to_date that back or think of another trick.

Hold on to your datatype (if they are correct of course) for as long as you can. Use to_char for displaying/formatting purposes, not for calculations (if possible).
Re: Compare oracle timestamp [message #336944 is a reply to message #336700] Tue, 29 July 2008 07:34 Go to previous message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

I agree. Thanks for correction.

regards
Previous Topic: Grouping in sets of 100 rows
Next Topic: Set a order to execute triggers of the same type
Goto Forum:
  


Current Time: Wed Dec 07 16:12:52 CST 2016

Total time taken to generate the page: 0.11672 seconds