Home » Developer & Programmer » Forms » Formatting Time from Date within SQL create Table statements
| Formatting Time from Date within SQL create Table statements [message #155647] |
Tue, 17 January 2006 16:09  |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
Ive had alook through the books available to me. The problem im presented with is formatting time from the date datatype but not via insert statements. Basically, is there any means to format to time(HH24:MM preferred) within a create table statement? Eventually im looking to conduct calculations using times in order to create business rules. Im hoping that i can restrict the length of time between two time values using triggers and program units.
Any help is much appreciated.
Thanks
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #155650 is a reply to message #155647] |
Tue, 17 January 2006 16:19  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
I'm not sure what you're asking.
If you want to store time down to the second, then use a DATE datatype.SQL> CREATE TABLE t (d DATE)
2 /
Table created.
SQL> INSERT INTO t VALUES (SYSDATE);
SQL> INSERT INTO t VALUES (SYSDATE + 1/24);
SQL> INSERT INTO t VALUES (SYSDATE + 1);
SQL> SELECT TO_CHAR(t.d
2 , 'Dy DD-MON-YYYY fmHH:fmMI:SS AM') dates
3 FROM t
4 /
DATES
---------------------------
Tue 17-JAN-2006 5:14:48 PM
Tue 17-JAN-2006 6:14:48 PM
Wed 18-JAN-2006 5:14:48 PM
SQL>
Can you give us some CREATE TABLE and INSERT statements, and tell us what it is that you're trying to do?
|
|
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #155669 is a reply to message #155647] |
Tue, 17 January 2006 17:39 |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
The only problem i have there is:
When i get a user to input data into my Oracle Forms, the formatting 'tags' wont be present and basically the form wont work. I just wondered if i could format time in the database thats all.
To give you some background info:
Im creating a dissertation piece looking into the techniques and functions of Oracle Forms builder. As a subject im relating the database to the United Kingdom Heavy Goods Vehicle driver's hour's regulations('Government red tape really').
For an example;
A driver of a truck must adhere to the regulations, one of which states that a minimum of a 45minute break must be taken after 4 1/2 hours driving. However, this can be broken down. So he might drive 1 hour, have 15 mins break and drive for 3 1/2 hours before having the remaining 30mins of his break.
So,
Ive structured a table so that there are various drive times and rest times.IE
CREATE TABLE Working_Day(
The_Date DATE NOT NULL,
FK_Driving_Lic VARCHAR(10) NOT NULL,
FK_Vehicle_Reg VARCHAR(9) NOT NULL,
DriveTime1 DATE ,
RestPeriod1 DATE ,
DriveTime2 DATE ,
RestPeriod2 DATE ,
DriveTime3 DATE ,
RestPeriod3 DATE ,
DriveTime4 DATE ,
RestPeriod4 DATE ,
DriveTime5 DATE ,
TotalDriveTime NUMBER (4,2) ,
DailyRestPeriod NUMBER (4,2) );
There are constraints but these aint important now. All id like is to be able to set the orders of events in sequence and set the times in the releant fields.
So
Driver starts: 07:00(drivetime1)
First break: 10:00(restperiod1)
and so forth, just so i can create calculations to represent the regulations and produce error handling if the rules are breached.
It might be the case that there is a feature in Forms Builder for this purpose and my post is in the wrong section.
Thanks again.
Upd-mod: Add code tags.
[Updated on: Wed, 18 January 2006 18:25] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #155835 is a reply to message #155699] |
Wed, 18 January 2006 20:35 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
You say you are doing a 'dissertation piece'. Well, I hope that either, you condescend to use apostrophes and correct capitalisation or they mark you do for it, because it is annoying to try and read your current text.
Now, with that off my chest, I find your current table structure deficient in a number of ways. The most important is that it probably won't handle someone driving from 10pm to 2am the following day and where you ask the question "Who was driving on day 'n'?" as, with your current structure, you would, most probably, search by 'The_Date'.
We could fill reams of paper with different table structures and the philosophy behind them but I would like to propose the following oversimplified two table structure.
CREATE TABLE DRIVERS
(
DRIVER_ID NUMBER(10) NOT NULL,
FK_DRIVING_LIC VARCHAR2(10 BYTE) NOT NULL,
FK_VEHICLE_REG VARCHAR2(9 BYTE) NOT NULL
);
CREATE TABLE TRIP_TIMES
(
DRIVER_ID NUMBER(10) NOT NULL,
TRIP_START DATE,
TRIP_END DATE
);
You could define another table 'Trips' that fits between 'Drivers' and 'Trip_Times'. It is not necessary absolutely necessary but could be used to hold accumulated trips.
CREATE TABLE TRIPS
(
TRIP_ID NUMBER(10) NOT NULL,
DRIVER_ID NUMBER(10) NOT NULL,
TRIP_TOTAL DATE,
REST_TOTAL DATE
);
CREATE TABLE TRIP_TIMES
(
TRIP_ID NUMBER(10) NOT NULL,
TRIP_START DATE,
TRIP_END DATE
);
For a particular driver and trip you would record the start and end time (and date, but this could be defaulted during data entry to save key strokes, or logic could be built into the code so that if the end time is less than the start time and the total is less than 5 hours then it is assumed to be the next day.
Use a display format of 'dd-Mon-yyyy HH24:MI' for the start and end times, but in a Pre-Text-Item trigger change the format for the start time to 'dd/mm/yyyy HI24:MI' and put it back to normal in a Post-Text-Item trigger. For the end time field, set the format in a Pre-Text-Item to 'HH24:MI' and add the hours and minutes to the truncated start date. Attention needs to be taken of the condition detailed in the previous paragraph.
A few records.
INSERT INTO TRIP_TIMES ( TRIP_ID, TRIP_START, TRIP_END ) VALUES (
1, TO_Date( '01/01/2006 02:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),
TO_Date( '01/01/2006 04:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TRIP_TIMES ( TRIP_ID, TRIP_START, TRIP_END ) VALUES (
1, TO_Date( '01/01/2006 05:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),
TO_Date( '01/01/2006 06:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TRIP_TIMES ( TRIP_ID, TRIP_START, TRIP_END ) VALUES (
1, TO_Date( '01/01/2006 10:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
TO_Date( '01/01/2006 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TRIP_TIMES ( TRIP_ID, TRIP_START, TRIP_END ) VALUES (
2, TO_Date( '01/01/2006 10:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
TO_Date( '01/01/2006 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TRIP_TIMES ( TRIP_ID, TRIP_START, TRIP_END ) VALUES (
3, TO_Date( '01/01/2006 10:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
TO_Date( '01/01/2006 12:00:00 PM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
To get the duration of a trip use:
select to_char (trunc (sysdate)
+ (max (tt.trip_end) - min (tt.trip_start) ),
'HH24:MI') "Trip Duration"
from trip_times tt
where 1 = 1
and tt.trip_id = 1;
To get total time travelled:
select to_char (trunc (sysdate) + sum ( (tt.trip_end) - (tt.trip_start) ),
'HH24:MI') "Driving Time"
from trip_times tt
where 1 = 1
and tt.trip_id = 1;
To get total of rest periods:
select to_char (trunc (sysdate)
+ sum ( (tt2.trip_start) - (tt1.trip_end) ),
'HH24:MI') "Rest Time"
from trip_times tt1, trip_times tt2
where 1 = 1
and tt1.trip_end = (select max (tt3.trip_end)
from trip_times tt3
where 1 = 1
and tt3.trip_end <= tt2.trip_start)
and tt1.trip_id = tt2.trip_id
and tt1.trip_id = 1;
Work with these and come back with any questions.
David
[Updated on: Wed, 18 January 2006 20:46] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #155959 is a reply to message #155647] |
Thu, 19 January 2006 20:39 |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
Going back to the problem. What you have suggested is much better than my original idea. However, the main reason I have to break each period down is because eventually I have to constrain each of these periods. Well, when I say constrain, I actually mean produce some means as to educate the end user. I don't think the answer to my concerned content would be to prevent data being entered. Instead, it should acknowledge what has been entered and then state to the user, whether or not the data entered is that of legal or illegal operating times. I will implement this part using alert messages or I have thought of constructing something on the form that prints the message. I don't mean the panel at the foot of the form either.
Just to let you know, I've been working with PL/SQL for 6 months now but it seems to have me hooked.
Anyway, to expose you as to the sorts of business rules I have to satisfy;
1.A driver can work for a maximum of 56 hours in one week but this can only be extended to a maximum of 90hours over two weeks.
2. A driver must have a rest period inbetween driving shifts of 11hours but this can be reduced to 9hours three times a week.
3.A daily rest period(while on shift) must be atleast 15minutes).
There are a good few more but I just thought I'd let you know some of the problems which lay in wait.
The tables that you have structured for me, should I still break them up into periods of drive time and rest using attributes? Or is there a means as to structure seperate tables in order to eventually create the constraints in a better manner?
Thanks
[Updated on: Thu, 19 January 2006 20:40] Report message to a moderator |
|
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #156026 is a reply to message #155647] |
Fri, 20 January 2006 06:21 |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
First question; The system is going to be a means of educating transport managers as to the regulations. Therefore, it fits your second suggestion about 'a driver last week cocking up and working to much.' By the way, I wish I had thought about the cab bleeping system because it sounds a little bit more like a product.
Second question; A working week is defined from 00:00 hours on the Monday morning until the following Sunday at 24:00hours.
If you like I could send you a power point presentation on this stuff? I have them because I've recently studied and passed what is known as a Certificate of Professional Competence(CPC) in road transport. Just a transport manager's qualification that's all.
If you have time to look through it and make suggestions, it will be greatly appreciated. I'm not asking you to do my work for me because i will be including this thread in my appendices. I just think being helped on here compensates for my lack of experience.
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #162126 is a reply to message #155647] |
Wed, 08 March 2006 20:26 |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
Hi,
Long time posting I know!
I've created the database ERD model within Deisgner 9i. As previously discussed, I have changed the table structure so that i now have two tables forming the ex-'working day'. They are very similar to DJ Martin's example and include 'Trips' and 'Trip Times'. The question I'd like to ask now is how do I distinguish each 'working day' as it were. It's not the issue that a driver might start at 10pm one night and finish the following day. The point I'm trying to establish is how I 'group' together the 'trip times' in order to form a 'working day' which i can then constrain with triggers and program units.
For example:
You might have three trip times for one day. These trip times should then form the trip or total trip for that 'day'. Do I need a seperate foreign key constriant between each? This would help 'bind' the times together because they all form that particular days driving or work.
I'm finding this hard to explain! Reply if you need more information to help my cause:) Thankyou
P.S The proposal of the feuerstein book was a massive help. I'm trying to purchase one now. I have a loaned copy though:)
|
|
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #162532 is a reply to message #155647] |
Fri, 10 March 2006 19:30 |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
CREATE TABLE trips (
total_drive_time NUMBER,
trip_id NUMBER NOT NULL,
total_rest_time VARCHAR2(240),
ve_registration VARCHAR2(8) NOT NULL,
dr_driver_id NUMBER(6) NOT NULL)
CREATE TABLE trip_times (
drive_or_work VARCHAR2(1) DEFAULT 'D' ,
times_id NUMBER(5) NOT NULL,
tt_start DATE,
finish DATE,
tr_trip_id NUMBER NOT NULL)
/
I havent as yet made the 'flag' within trip times restricted because I have not found that feature in Designer yet.
Going back to what I asked. I'm curious to know how to group all the trip times for that particular days work so that I can then make program units to calculate complete totals of driving and working hours. Do I need it in your opinion?
Thankyou:)
[Updated on: Fri, 10 March 2006 19:35] Report message to a moderator |
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #162852 is a reply to message #162532] |
Mon, 13 March 2006 21:14 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Okay, compared with the tables defined in my message 155835, I see that you have added the vehicle registration to 'trips' and 'drive_or_work' indicator to 'trip_times'. I guess that you are using 'times_id' to give you a unique key to 'trip_times'. I suggest that instead you make the 'tt_start' a 'not null' field and drop the 'times_id'. I have found these sort of artificial extras annoying to implement and maintain. The records will sort naturally if you use the 'tt_start' as part of the key.
I see that you defined 'total_rest_time' as varchar2(240) and 'total_drive_time' as number. I suggest that they both be the same type and always give a size to numbers. Number(4) will hold 1440 which the number of minutes in a day, I don't think we need to be more accurate that a minute. I suggested that these fields be of type date but as RJ.Zijlstra suggested you can use the 'INTERVAL DAY TO SECOND'. Your call, but please don't use varchar2(240).
Concerning your question "I'm curious to know how to group all the trip times for that particular days work so that I can then make program units to calculate complete totals of driving and working hours." please reread my message 155835 as I give the 'select' statements to determine this information for you.
David
PS Below are table definitions with the fields ordered as I, as a developer) would like to see them ordered.CREATE TABLE trips
(
TRIP_ID NUMBER NOT NULL,
DR_DRIVER_ID NUMBER(6) NOT NULL,
VE_REGISTRATION VARCHAR2(8) NOT NULL,
TOTAL_REST_TIME NUMBER(5),
TOTAL_DRIVE_TIME NUMBER(5)
);
CREATE TABLE TRIP_TIMES
(
TR_TRIP_ID NUMBER NOT NULL,
TT_START DATE NOT NULL,
TT_FINISH DATE,
DRIVE_OR_WORK VARCHAR2(1) DEFAULT 'D'
); |
|
|
|
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #164771 is a reply to message #155647] |
Sat, 25 March 2006 19:54 |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
Hello,
I'm now in full flow with making this 'product.' There are quite a few questions that I've tried to answer myself but have come to a 'stand-still.' Might be the case that I'm just panicking because I am now producing the same product using MySQL 5.0 and PHP.Thus,double the work load. Basically, the piece I'm doing is a comparison and I have to implement both in order to create a critical analysis and justify which is best(Oracle Forms or php MySQL?).
Anyway, the first question I have regards the calculations you created a while back. Can you explain to me why the "trunc (sysdate)" part of the calculation is needed? Especially the "sysdate" because while the inserted example values are dated "1/1/2006," why should todays date be relevant?
I just need to fully understand what is going on because it is affecting the way I'm trying to create the triggers, I need to satisfy the business rules. The first business rule I'm trying to satisfy is the 4 1/2 Hour maximum "trip_time" rule. I've gone about this using a before statement level trigger.
The second question I have regards the actual forms. I'd like to create a login page for the rest of my forms. I've done one in php with MySQL which was simple but now i need an equivalent Forms version. Is there any examples I can work from anywhere, that you know of? Is it the same principle as MySQL where you create a new table containing users or can you use the "users from dual" ?
Thanks in advance for any feedback, your help is very much appreciated:)
|
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #164843 is a reply to message #164771] |
Mon, 27 March 2006 00:48 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Which is 'best' can be highly subjective. You should also consider Oracle's Application Express (previously named as HTML_DB). You keep the current Oracle table structures but if the database is less than 4G and runs on a single processor, it is a free product.
Quote: "I've gone about this using a before statement level trigger" I don't understand. Which Forms trigger is this?
You are working on "trying to satisfy is the 4 1/2 Hour maximum "trip_time" rule". True. You probably don't need the trunc(sysdate). I must have been thinking of something else at the time. As you are only working with the 'hours and minutes' then just make sure that your fields are defined as date-time (versus date) and the mathematics should work for you.
I ALWAYS do my rule testing in a When-Validate-Item. You can't test something until you have the data and the first trigger that can fire after the data is entered or changed is the WVI trigger. This trigger can only perform "SELECT statements, and unrestricted built-ins" but normally this is enough. If you HAVE to jump to another field then you store the name of the field somewhere and then create a timer which will 'action' after the WVI trigger is finished. Question: If a driver DOES exceed 4 1/2 hours, what can you do?
Concerning a 'logon' screen, please search this forum for 'logon' and 'login'. This is a common question. Basically you create a role in the database with access permissions to the tables and procedures, and then create a user in the database, and assign the[n]m this application's role. You can have multiple roles - data entry role, data search role, and administrator role - each has different powers and data access levels.
David
[Updated on: Thu, 30 March 2006 00:17] Report message to a moderator |
|
|
|
| Re: Formatting Time from Date within SQL create Table statements [message #165375 is a reply to message #155647] |
Wed, 29 March 2006 13:04 |
robhulse
Messages: 35
Registered: November 2005
|
Member |
|
|
Sorry, I keep switching my ideas with making the database more secure and putting the triggers at server level, but no you are right. I should use Forms builder as the 'RAD' it is and completely depend upon its features. Concerning your first suggestion; My study only ever planned to compare the two I have chosen. I could have done HTML DB, yes. I could also have done a web application in javabeans, which is something I'm learning now. I will make this a suggestion in my 'futher work' though.
Your question;
'If a driver DOES exceed 4 1/2 hours, what can you do?'
I was going to implement an alert with a message. Your bleeping system is a little more sophisticated  . However, I just have to advise and notify the user from an educational point of view.
Anyway, I'll let you know how I'm getting on!
Thanks again
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 08:19:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
I'll correct my sloppy grammer in future. Thanks again.