如何在Oracle sql查询中编写mysql TIMESTAMPDIFF函数 [英] How to write mysql TIMESTAMPDIFF function in Oracle sql query
问题描述
我在 Mysql 中有查询,它使用表中的 TIMESTAMPDIFF 返回分钟.但现在我已将数据迁移到 Oracle.所以我想使用相同的查询来获取 Oracle 表中的 TIMESTAMPDIFF.Oracle 也不支持 mysql 中的 NOW() 函数.查询中的 PROCESS_START_DATE 列包含包含日期和时间的数据.我在 oracle 中尝试了 EXTRACT 功能,但是没有用.这是我的查询:
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
推荐答案
你可以这样做:
--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.
此查询计算一年中的分钟数:
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;
所以,当运行这个查询结果是525605
,一年多五分钟.所以它看起来有效.
So, when run this query result is 525605
, five minutes more than a year. So it looks to be working.
这篇关于如何在Oracle sql查询中编写mysql TIMESTAMPDIFF函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!