CODEWITHSUNDEEP
sqloracle-databasetimestamp
hoisu
hoisu

Reputation: 325

Oracle Date/Time manipulation query

I have problem finding a solution in oracle SQL for my query :

This is an example of the query I need :

Select Result_Date as hour , Type , Count(Id) as Number From Results 
Where Result_Date Between Today'06:00' And today'14:00'
group by result_date , type

Result date contains the hour , the format of the result date is timestamp . How can i accomplish this query without entering the date in the between statement , only the hours between I want to search for today .

If you have any thoughts please let me know ,any idea is welcome .

Upvotes: 1

Views: 419

Answers (2)

hoisu
hoisu

Reputation: 325

EDIT ,This is what worked for me 

   Select to_number(extract(hour from Result_Date)) as hour , Type , Count(Id) as Number From Results Where To_Date(Trunc(Result_Date)) = To_Date(Trunc(Current_Timestamp))
        and to_number(extract(hour from Result_Date)) Between 6 and 14     
            group by  to_number(extract(hour from )Result_Date) as hour  , type

@Gordon Linoff , thank you dor the suggestion with trunk , it helped me allot

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use the trunc() function on timestamps, so this might do what you want:

Select trunc(Result_Date, 'HH24') as hour , Type , Count(Id) as Number From Results 
Where trunc(Result_Date, 'HH24') Between '06:00' And '14:00' and
      trunc(Result_Date) = trunc(sysdate)
group by trunc(Result_Date, 'HH24'), type ;

Note:

trunc() on a date defaults to the day. You can add the specific format for this if you like.

Upvotes: 3

Related Questions