[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1845
  • Last Modified:

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
 
0
Cosine_Consultants
Asked:
Cosine_Consultants
1 Solution
 
anand_2000vCommented:
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
0
 
earth man2Commented:
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;
0
 
Mark GeerlingsDatabase AdministratorCommented:
Which version of Oracle do you have? Oracle9 and 10 have more date formats and operators than earlier versions of Oracle.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
jrb1Commented:
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;
0
 
Cosine_ConsultantsAuthor Commented:
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.


 

0
 
earth man2Commented:
SELECT  O.ORDERID, O.QUANTITY, O.STATUS, O.ETIME ,
O.DATEORDERED + O.ETIME * INTERVAL '1'  MINUTE - SYSDATE AS CTIME,
SYSDATE - O.DATEORDERED AS ETIME_SIGN,
to_char( O.ETIME * INTERVAL '1' MINUTE, 'HH:MI:SS' ) AS MTIME_SIGN
FROM ORDERS O, CUSTOMERS C
WHERE
O.STATUS = 'T' AND
C.CUSTOMERID = O.CUSTOMERID;
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now