Home » SQL & PL/SQL » SQL & PL/SQL » how to manipulate greater than 24 hour clock in oracle (Forms10g 10.1.2.0.2 , windows xp)
 how to manipulate greater than 24 hour clock in oracle [message #516048] |
Thu, 14 July 2011 14:32  |
 |
aaichah
Messages: 24
Registered: July 2011
Location: Ottawa
|
Junior Member |
|
|
Hi,
I have an Oracle application that deals with a 29 hour clock.
so the days begin at 5am instead of midnight.
I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, eventhough there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this code:
SELECT *
FROM WORK.WORK_UNIT
WHERE (to_char (current_garage) = :entry_blk.curr_garage
OR 'all' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND ((package_id > 0 and work_code = 99) OR
(package_id is null and work_code = 1))
and Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour)
I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI')
will give me data up to 2 hours
Thank you for your help
|
|
|
|
|
|
|
|
| Re: how to manipulate greater than 24 hour clock in oracle [message #516241 is a reply to message #516057] |
Fri, 15 July 2011 12:23  |
|
aaichah
Messages: 24
Registered: July 2011
Location: Ottawa
|
Junior Member |
|
|
Guys,
you are right, it is very confusing, but the system is already in place, and that is how it works.
Actually, it is a 27hour clock, it is bus system, scheduling I am working on. I am just taking a form they already have, and making it display certain records up to a certain #hours that the
user chooses. And it turned out that the system is developed with a 27hr clock. When I asked my manager what is the reason behind using 27hr clock, he replied:
"Since yesterday bus is coming back at 2:00am today"
so since a shift might starts today, and won't end till after midnight, they have to make the day a 27hr clock.
I hope I explained it well
|
|
|
|
| Re: how to manipulate greater than 24 hour clock in oracle [message #516258 is a reply to message #516241] |
Fri, 15 July 2011 14:23 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You did, but that's stupid anyway. Oracle subtracts date values just fine. But never mind me, my complaints won't solve your problem.
Could you, please, provide a simple test case? That would include CREATE TABLE and INSERT INTO several sample records. Based on it, show what you expect as a result. There are really smart guys here who are capable of writing extraordinary SELECTs and I'm sure someone will assist.
Also, at the moment it seems to be pure SQL problem (and not Forms one), so I'll move it to SQL forum. If it turns out that Forms is to be involved, we can move it back some time later.
|
|
|
|
|
|
| Re: how to manipulate greater than 24 hour clock in oracle [message #516644 is a reply to message #516048] |
Tue, 19 July 2011 15:20 |
|
aaichah
Messages: 24
Registered: July 2011
Location: Ottawa
|
Junior Member |
|
|
Hi,
I put togetther a sample table with few inserts to show an example of what I am working on. The original table has many other columns, but those are the most importatnt ones. There are:
1-work_date
current date we are trying to get the 'open work' pieces
2-on_time
a number field which holds the time the shift starts up to 27hrs
3-start_dt
date field which recalculate the date and the time in a 12 hour format. if the time falls after 2359, it sets the date to tomorrow's date and starts the time at 00(midnight) or after midnight for the next day. (but actually, the work date is still todays date)
If you look at the data, you understand what I mean
Now, my query works as long as I do not have a record that have greater than 2359 for on_time.
If it does have such a record, no records will be returned, eventhough I have records before midnight.
I guess the reason is because I am trying to search using the format HH24MM, and when I get a greater than 24 hr record, the whole query fails.
Here is where the query fails:
SELECT *
FROM WORK.WORK_table
WHERE route >= 0 AND run >= 0
AND work_date = TO_DATE('20110719', 'YYYYMMDD')
AND on_time <= TO_CHAR(SYSDATE + 8/24, 'HH24MI')
where 8 or any number I choose will determine how many
open pieces of work I have for the next 8 hours lets say.
So if all the records returned fall before midnight, or
in the current day, I have no problem. But if one record falls
after, the whole query fails, and no record will be returned.
I am not sure how to make it return both before and after midnight.
Tried to use the Start_dt column, but I am not good in time and date manipulation
Please help
Thank you
|
|
|
|
|
|
| Re: how to manipulate greater than 24 hour clock in oracle [message #517006 is a reply to message #516650] |
Thu, 21 July 2011 14:31 |
|
aaichah
Messages: 24
Registered: July 2011
Location: Ottawa
|
Junior Member |
|
|
Thank you for replying
The MOD function helped a lot
I am still confused myself
I got the query to work to show data for both befre midnight and after midnight. But since my form has a timer and refreshes every few senconds, and scrolls to the last record, then rebuild the query, with the last 25 displayed records added to the original query, this way the scrolling works like from first recors till last then it restarts at the first after the last and so on.
In summary, it is a display screen for drivers to see open work pieces by garage.
here is the where_clause, but I am still having problem to rebuild the query after it reaches the last record if we hit midnight:
=======================================================
where_cl := '(to_char (current_garage) = :entry_blk.curr_garage
OR ''all'' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= 2359
UNION ALL
(SELECT ROWID, WORK_DATE, ROUTE,RUN,CURRENT_GARAGE,GARAGE_TAG_ID,PACKAGE_ID,
ON_LOCATION,ON_TIME,OFF_TIME,PLATFORM_TIME,OFF_LOCATION
FROM work_unit
WHERE route >= 0 AND run >= 0
AND Nvl (Emp_No, 0) = 0 AND work_date = :entry_blk.p_work_date
AND TO_CHAR(start_dt, ''YYYYMMDD'') = TO_CHAR(:entry_blk.p_work_date, ''YYYYMMDD'') + 1
AND on_time > 2359
AND MOD(on_time,2400) <= TO_CHAR(SYSDATE + :entry_blk.after_mid/24, ''HH24MI''))';
I will update this thread as I progress
Thanks again
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Apr 28 22:58:59 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
