Cosine_Consultants
asked on
Timestamp, Date Question
Hi,
I'm trying to migrate MaxDB Stored Procedures to Oracle PL/SQL
In MaxDB I had the following Selct query:
SELECT O.ORDERID ,O.QUANTITY ,O.STATUS , O.ETIME ,
TIMEDIFF(MAKETIME(0,O.ETIM E,0), TIMEDIFF (TIMESTAMP,O.DATEORDERED) ) AS CTIME,
TIMEDIFF (TIMESTAMP,O.DATEORDERED) AS ETIME_SIGN,
MAKETIME(0,O.ETIME,0) AS MTIME_SIGN
FROM ORDERS O,CUSTOMERS C
WHERE O.STATUS = True
AND C.CUSTOMERID = O.CUSTOMERID;
ETIME is Integer and DateOrdered is Timestamp and TimeStamp is the equivalent of Current_Timestamp in Oracle
Now the definition of TIMEDIFF in maxdb is
-------------------------- ---------- ---------- ---------- -
TIMEDIFF(t,s) is a time function that calculates the time value between a start and end time.
t and s: time_or_timestamp_expressi on
Both arguments must have the same data type, i.e. they must be either a time value or a timestamp value.
Result of the TIMEDIFF(t,s) function
Positive difference between t and s
Time value
t or s are timestamp values for alphanumeric values that match the current timestamp format.
The dates contained in the timestamp value are used to calculate TIMEDIFF(t,s).
Difference of more than 9999 hours
Number of hours modulo 10000
t or s is NULL value
NULL value
-------------------------- ---------- ---------- ---------
The definition of Maketime is
-------------------------- ---------- ----
MAKETIME(h,m,s)
MAKETIME(h,m,s) is a time function that calculates a time value from the total number of hours, minutes, and seconds.
Result of the MAKETIME(h,m,s) function
h,m,s is NULL value
NULL value
h,m,s is special NULL value
Error message
-------------------------- ---------- -------
What I want is to write the select query in Oracle? How can I do that?
Thanks
I'm trying to migrate MaxDB Stored Procedures to Oracle PL/SQL
In MaxDB I had the following Selct query:
SELECT O.ORDERID ,O.QUANTITY ,O.STATUS , O.ETIME ,
TIMEDIFF(MAKETIME(0,O.ETIM
TIMEDIFF (TIMESTAMP,O.DATEORDERED) AS ETIME_SIGN,
MAKETIME(0,O.ETIME,0) AS MTIME_SIGN
FROM ORDERS O,CUSTOMERS C
WHERE O.STATUS = True
AND C.CUSTOMERID = O.CUSTOMERID;
ETIME is Integer and DateOrdered is Timestamp and TimeStamp is the equivalent of Current_Timestamp in Oracle
Now the definition of TIMEDIFF in maxdb is
--------------------------
TIMEDIFF(t,s) is a time function that calculates the time value between a start and end time.
t and s: time_or_timestamp_expressi
Both arguments must have the same data type, i.e. they must be either a time value or a timestamp value.
Result of the TIMEDIFF(t,s) function
Positive difference between t and s
Time value
t or s are timestamp values for alphanumeric values that match the current timestamp format.
The dates contained in the timestamp value are used to calculate TIMEDIFF(t,s).
Difference of more than 9999 hours
Number of hours modulo 10000
t or s is NULL value
NULL value
--------------------------
The definition of Maketime is
--------------------------
MAKETIME(h,m,s)
MAKETIME(h,m,s) is a time function that calculates a time value from the total number of hours, minutes, and seconds.
Result of the MAKETIME(h,m,s) function
h,m,s is NULL value
NULL value
h,m,s is special NULL value
Error message
--------------------------
What I want is to write the select query in Oracle? How can I do that?
Thanks
there is no such functions in Oracle. However if you provide some sample outputs we will be able to help you write the code for the same
Oracle SQL does not have a boolean type so best use a CHAR(1) column with check constraint in ('T','F')
Something like.
SELECT O.ORDERID, O.QUANTITY, O.STATUS, O.ETIME ,
SYSDATE - O.DATEORDERED - O.ETIME * INTERVAL '1' MINUTE AS CTIME,
SYSDATE - O.DATEORDERED AS ETIME_SIGN,
O.ETIME * INTERVAL '1' MINUTE AS MTIME_SIGN
FROM ORDERS O, CUSTOMERS C
WHERE
O.STATUS = 'T' AND
C.CUSTOMERID = O.CUSTOMERID;
Something like.
SELECT O.ORDERID, O.QUANTITY, O.STATUS, O.ETIME ,
SYSDATE - O.DATEORDERED - O.ETIME * INTERVAL '1' MINUTE AS CTIME,
SYSDATE - O.DATEORDERED AS ETIME_SIGN,
O.ETIME * INTERVAL '1' MINUTE AS MTIME_SIGN
FROM ORDERS O, CUSTOMERS C
WHERE
O.STATUS = 'T' AND
C.CUSTOMERID = O.CUSTOMERID;
Which version of Oracle do you have? Oracle9 and 10 have more date formats and operators than earlier versions of Oracle.
I think we have everything you need in Oracle. The status won't be boolean, can make it Y/N. What do you want for all the other fields? MTIME_SIGN for example? There is no timestamp. We could format the value as a time. Is that OK? For example, it appears that o.etime has some number of minutes. We could add the minutes divided by 1440 to the current date or to 1/1/1900. To the current date, look at this:
select to_char(trunc(sysdate) + 200/1440, 'hh24:mi') from dual;
It returns: 03:20. Is that what you want for MTIME_SIGN?
For ETIME_SIGN, what is expected? We can use sysdate for timestamp. Is dateordered also a timestamp? We can take:
sysdate - dateordered
Which will give us the difference between the value. The issue then becomes displaying that value in a way that is meaningful. The last issue is CTIME. Is this just subtracting the current time from the etime? One way to do this is to take the current time:
sysdate - trunc(sysdate)
And then subtract out the other minutes
sysdate - trunc(sysdate) - o.etime
This will give you the fraction of a day between the two . Again, how should this be presented. It could be added to some date and displayed: Assume that o.etime if 2000, right now I get:
select to_char(trunc(sysdate) + (sysdate-trunc(sysdate)-20 00),'hh24: mi:ss') from dual;
19:15:10
What are you getting for CTIME? It looks like you are taking the time different between current timestamp and dateordered timestamp. We can do:
sysdate - o.dateordered
That would return the actual difference in number of days and time. Then you subtract the end time from this. I'm not sure if it's what you expect though:
sysdate - o.dateordered - o.etime/1440
So, my answer is, that it depends exactly on what you want, but something like this is similar to your answer:
SELECT O.ORDERID ,O.QUANTITY ,O.STATUS , O.ETIME ,
sysdate - o.dateordered - o.etime/1440 AS CTIME,
to_char(trunc(sysdate) + (sysdate-trunc(sysdate)-o. etime),'hh 24:mi:ss') AS ETIME_SIGN,
to_char(trunc(sysdate) + o.etime/1440, 'hh24:mi') AS MTIME_SIGN
FROM ORDERS O,CUSTOMERS C
WHERE O.STATUS = True
AND C.CUSTOMERID = O.CUSTOMERID;
select to_char(trunc(sysdate) + 200/1440, 'hh24:mi') from dual;
It returns: 03:20. Is that what you want for MTIME_SIGN?
For ETIME_SIGN, what is expected? We can use sysdate for timestamp. Is dateordered also a timestamp? We can take:
sysdate - dateordered
Which will give us the difference between the value. The issue then becomes displaying that value in a way that is meaningful. The last issue is CTIME. Is this just subtracting the current time from the etime? One way to do this is to take the current time:
sysdate - trunc(sysdate)
And then subtract out the other minutes
sysdate - trunc(sysdate) - o.etime
This will give you the fraction of a day between the two . Again, how should this be presented. It could be added to some date and displayed: Assume that o.etime if 2000, right now I get:
select to_char(trunc(sysdate) + (sysdate-trunc(sysdate)-20
19:15:10
What are you getting for CTIME? It looks like you are taking the time different between current timestamp and dateordered timestamp. We can do:
sysdate - o.dateordered
That would return the actual difference in number of days and time. Then you subtract the end time from this. I'm not sure if it's what you expect though:
sysdate - o.dateordered - o.etime/1440
So, my answer is, that it depends exactly on what you want, but something like this is similar to your answer:
SELECT O.ORDERID ,O.QUANTITY ,O.STATUS , O.ETIME ,
sysdate - o.dateordered - o.etime/1440 AS CTIME,
to_char(trunc(sysdate) + (sysdate-trunc(sysdate)-o.
to_char(trunc(sysdate) + o.etime/1440, 'hh24:mi') AS MTIME_SIGN
FROM ORDERS O,CUSTOMERS C
WHERE O.STATUS = True
AND C.CUSTOMERID = O.CUSTOMERID;
ASKER
The input to this is going to be
DateOrdered = 2005-05-16 10:33:31.489000 (the timestamp that the order has been placed)
Etime = 29 (minutes)
The Output to this is
CTIME = 00:27:43 the remaining time needed to prepare an order
ETIME_SIGN = 00:01:17 the time passed from the time the order has been placed (DateOrdered - see above)
MTIME_SIGN = 00:29:00 the input ETIME in hh:mm:ss format
I hope that this is going to help.
DateOrdered = 2005-05-16 10:33:31.489000 (the timestamp that the order has been placed)
Etime = 29 (minutes)
The Output to this is
CTIME = 00:27:43 the remaining time needed to prepare an order
ETIME_SIGN = 00:01:17 the time passed from the time the order has been placed (DateOrdered - see above)
MTIME_SIGN = 00:29:00 the input ETIME in hh:mm:ss format
I hope that this is going to help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.