2

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.

  • @Anuruddha Please create an example set of data on sqlfiddle.com – Philᵀᴹ Jan 5 '16 at 16:30
0

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') ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.