Oracle: Time range in query

Experts,

I need assistance with writing a query for the following criteria.

Any source of Construction created between 1am – 5:30am would be considered normal maintenance (non-outage).
Any source of Construction outside of those hours would be considered an outage.

I cant seem to add the time range properly.  Any suggestions?

            (CASE
                WHEN UPPER(SOURCE) = 'CONSTRUCTION' 
                AND UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') 
                BETWEEN TO_DATE('01:00:00','HH24:MI:SS') and TO_DATE('05:30:00','HH24:MI:SS') THEN 'MNT'
                ELSE 'OUTAGE'
             END) AS FORM_TYPE,

Open in new window

Maliki HassaniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gerwin Jansen, EE MVETopic Advisor Commented:
Can't test atm but, something like this maybe:

(CASE
WHEN UPPER(SOURCE) = 'CONSTRUCTION'
AND TO_CHAR (DATE_CREATED, 'HH24:MI:SS')
                BETWEEN '01:00:00' and '05:30:00' THEN 'MNT'
                ELSE 'OUTAGE'
END) AS FORM_TYPE,
0
Maliki HassaniAuthor Commented:
ORA-01843: not a valid month

any other ideas?
0
Maliki HassaniAuthor Commented:
Going to try something like this that I found
select *
  from your_table
 where creation_date > sysdate - 7
   and to_char(creation_date, 'hh24:mi:ss') >= '06:45:00'
   and to_char(creation_date, 'hh24:mi:ss') <= '19:15:00'
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Maliki HassaniAuthor Commented:
no such luck!  still searching
0
flow01Commented:
your query does not work because you miss the day -part  of a date

select  to_char(TO_DATE('01:00:00','HH24:MI:SS'),'dd/mm/yyyy hh24:mi:ss') from dual
and you can see what you are actually comparing


Your date_created is not a date : that explains the  ORA-01843: not a valid month

replace "DATE_CREATED"  by "UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') "
in the post of gerwinjansen and it will  probably work

or calculate the part of the day in seconds after midnight
and compare with a range in seconds after midnight

           (CASE
                WHEN UPPER(SOURCE) = 'CONSTRUCTION'
                AND to_number(to_char(UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern'),'sssss'))
                       BETWEEN 1 * 3600 and (5.5 * 3600) THEN 'MNT'
                ELSE 'OUTAGE'
             END) AS FORM_TYPE,

--  (5,5) depending on your decimal separator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maliki HassaniAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.