Link to home
Start Free TrialLog in
Avatar of Cosine_Consultants
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.ETIME,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_expression
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
 
Avatar of anand_2000v
anand_2000v
Flag of India image

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;
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)-2000),'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),'hh24: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;
Avatar of Cosine_Consultants
Cosine_Consultants

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.


 

ASKER CERTIFIED SOLUTION
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial