How to convert following mysql query to oracle?

Question

I have a mysql query like following, Column time contains date in long format.

SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time,
SUM(CreatedInstanceCount) AS CreatedInstanceCount , SUM(InitializedInstanceCount) AS InitializedInstanceCount , 
SUM(ActiveInstanceCount) AS ActiveInstanceCount, 
SUM(TerminatedInstanceCount) AS TerminatedInstanceCount 
FROM MEMBER_COUNT WHERE ApplicationId='test' 
AND Time > ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000-'604800000' 
AND Time <= ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000) 
GROUP BY FROM_UNIXTIME( CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i'), ApplicationId

So far my oracle query is like this but the results are not same.

SELECT to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI') "Time", SUM(CreatedInstanceCount) 
AS "CreatedInstanceCount", SUM(InitializedInstanceCount) AS "InitializedInstanceCount", SUM(ActiveInstanceCount) AS "ActiveInstanceCount", 
SUM(TerminatedInstanceCount) AS "TerminatedInstanceCount" ,ApplicationId
FROM MEMBER_COUNT 
WHERE ApplicationId='test' AND to_date('1970-01-01 00','yyyy-mm-dd HH24:MI') +(time)/1000/60/60/24 between sysdate-7 and SYSDATE 
GROUP BY to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI'), ApplicationId
order by to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI') ;

This is my create table statement:

CREATE TABLE MEMBER_COUNT(Time number(20), ApplicationId VARCHAR2(150), ClusterAlias VARCHAR2(150), CreatedInstanceCount int, InitializedInstanceCount int, ActiveInstanceCount int, TerminatedInstanceCount int);

The problem is no of rows returned by oracle query are higher than the mysql query. I believe this has to do something with CEILING function.

Please help.

• Philᵀᴹ: @Anuruddha Please create an example set of data on sqlfiddle.com

Answer 1

I found the query:

SELECT TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160 * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI') "Time",
 SUM(CreatedInstanceCount) AS "CreatedInstanceCount",
 SUM(InitializedInstanceCount) AS "InitializedInstanceCount",
 SUM(ActiveInstanceCount) AS "ActiveInstanceCount",
 SUM(TerminatedInstanceCount) AS "TerminatedInstanceCount"
FROM MEMBER_COUNT
WHERE ApplicationId='wso2is-500-application'
AND to_date('1970-01-01 00','yyyy-mm-dd HH24:MI') +(ceil( TIME/ (20160 * 1000)) * 20160 )/60/60/24 BETWEEN sysdate-7 AND SYSDATE
GROUP BY TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160 * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI'),
 ApplicationId
ORDER BY TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160 * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI') ;