Compare oracle timestamp [message #336604] |
Mon, 28 July 2008 06:18  |
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 #336631 is a reply to message #336613] |
Mon, 28 July 2008 07:53   |
 |
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   |
Frank
Messages: 7901 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).
|
|
|
|