0

I have query in Mysql which return minutes using TIMESTAMPDIFF in table. But now i have migrated my data to Oracle. So i want to use the same query to get the TIMESTAMPDIFF in a table in Oracle. Oracle also dont support NOW() function in mysql. The PROCESS_START_DATE column in query have data which contains date and time. I tried EXTRACT function in oraclebut did not work. Here is my query :

    select * from(
    select trunc(abs(to_date('27/01/2015 08:00:00','dd/mm/yyyy hh:mi:ss') - PMS.PROCESS_START_DATE)*24*60),PM.NAME,PM.ENABLED
    from PROCESS_MONITOR_STATISTIC PMS
    JOIN PROCESS_MONITOR PM ON PM.ID=PMS.PROCESS_MONITOR_ID   
    WHERE PM.ENABLED=1 AND PM.NAME= 'WORKFLOWENGINE1'
    order by PMS.PROCESS_START_DATE desc
) 
where ROWNUM = 1
  • Is your column DATE (which includes a time) or TIMESTAMP (which is more precise)? That will affect whether you use `sysdate` or `systimestamp` instead of `now()`, and how the difference is converted to a single value representing the difference in (whole?) minutes. – Alex Poole Jan 26 '15 at 13:22
  • Its a datetime data type column and contains the value as 2014-12-30 14:44:24. And if you can please check the query i have set the row limit to1. So it will calculate the minute difference only for that row. –  Jan 26 '15 at 13:29
  • Just change `to_date('27/01/2015 08:00:00','dd/mm/yyyy hh:mi:ss')` by `sysdate` – Aramillo Jan 27 '15 at 14:51

1 Answers1

1

You can do something like this:

--in case you are working with dates
select trunc(abs(to_date('26/01/2015 08:00:00','dd/mm/yyyy hh:mi:ss') - sysdate)*24*60) from dual;

This represent difference in minutes between a date and now(sysdate) with dates.

--timestamp case
select abs(
extract (day from diff)*24*60 + extract (hour from diff)*60 + extract (minute from diff)) from
(select to_timestamp('27/01/2015 09:07:00','dd/mm/yyyy hh:mi:ss') - systimestamp diff from dual);

This represent difference in minutes between a date and now(systimestamp) with timestamp.

Edit:

This query calculate minutes in a year:

select 365*24*60 from dual -- this returns 525600

This is your query. i change the time. Check that the difference between these dates is one year and five minutes

select trunc(abs((to_date('26/01/14 09:00:00','dd/mm/yy hh24:mi:ss')-
to_date('26/01/2015 09:05:01','dd/mm/yyyy hh24:mi:ss'))*24*60)) from dual;

So, when run this query result is 525605, five minutes more than a year. So it looks to be working.

Aramillo
  • 3,176
  • 3
  • 24
  • 49
  • thank you for your reply. In the above query i want to calculate for minutes difference for 2 datetime column. Then i think i will use timestamp case right ? –  Jan 26 '15 at 13:33
  • that depends of datatype of your column, if `date` you use case 1, if `timestamp` use case 2. If you want to get difference between two columns you only have to replace sysdate or systimstamp by your column. – Aramillo Jan 26 '15 at 13:39
  • The first query assumes days are always 24 hours, which may not be the case (Depending on how the column is stored, and op's db settings. Heck, sometimes based on _user session_ settings, which can really cause problems). So @Ajay, the first question is, what timezones do you have to worry about? It looks like there might be something called `SYS_EXTRACT_UTC`, which should help, although it looks like it's for timestamps-with-timezone-info only. – Clockwork-Muse Jan 26 '15 at 14:08
  • Hi i realize that when i ran the query. What is 26/01/2015 08:00:00 , i dont understood from the query ? Do you know this ? I just want to extract minutes difference between the date which is given in my current table with the system date. –  Jan 26 '15 at 14:11
  • I really dont understand query now. My question was just so simple. But do i really need to write such a big query ? I just want to extract minutes difference between the date which is given in my current table with the system date –  Jan 26 '15 at 14:18
  • Yes, @ajay, in case of timestamps, this is the way of getting minutes between two dates, please take a look [here](http://stackoverflow.com/questions/11617962/calculating-difference-between-two-timestamps-in-oracle-in-milliseconds), also if you look carefully query it's not so big, only a sum on a query – Aramillo Jan 26 '15 at 14:20
  • So i have changed my question and updated the answer. Please check in question according to your way. But its showing the wrong result. The date column in my table is updating for example in ever 5 to 10 minutes automatically. And i want to calculate the minutes difference between this to the current date time. Do i am using the wrong brackets for order by or in condition ? –  Jan 26 '15 at 14:25
  • Its returning as 61000 –  Jan 26 '15 at 14:28
  • and what result should be? – Aramillo Jan 26 '15 at 14:29
  • See when i run the query in my table its showing 26.01.14 15:30:04 for the PROCESS_CREATION_DATE column. And when i run the complete query given by you to extract minutes its showing the result as 61000. If you calculate the diff for minutes from last 5 minute to the current time. Its not currect right. Thats i want to tell you. And in you query you have given as 26/01/2015 08:00:00 . Why you have given 08:00:00 ? –  Jan 26 '15 at 14:33
  • This is my query in mysql. SELECT TIMESTAMPDIFF(Minute,PMS.PROCESS_START_DATE, NOW()) from RATOR_IMPORT and i want to use this query in oracle. –  Jan 26 '15 at 14:36
  • 1
    @ajay - the query you currently have in your question gets 525209 if the most recent process start is 2014-01-26 15:30:04. For it to get 61000 the most recent process start would have to be 2014-12-15 00:20:00. We can't see your data so we can't tell where the confusion is; perhaps you're looking at the wrong record, or expecting one result per ID, or per name, or something? – Alex Poole Jan 26 '15 at 16:45
  • My query is showing the correct result now. But i dont understood why did u written specific date time in your query i.e 26/01/2015 08:00:00 ? I dont want to run the query for the specific day or time. I want to run it dynamically with the current date and time. How can i do that ? So i will always get the current minutes difference from current datetime - PROCESS_CREATION_DATE. In this the current datetime should not be defined like you defined in your query. It should be sysdate like in mysql its NOW(). –  Jan 27 '15 at 14:10
  • I changed the query in question now. Please it will be really helpful if you can tell me how to change the 27/01/2015 08:00:00 in query to a sysdate. When i used sysdate instead of your fixed datetime, i got an error as ORA-01849: hour must be between 1 and 12 –  Jan 27 '15 at 14:37
  • Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/69673/discussion-on-answer-by-aramillo-how-to-write-mysql-timestampdiff-function-in-or). – Taryn Jan 27 '15 at 14:48