CODEWITHSUNDEEP
mysqlsqlfunctiondb2
Sirhan
Sirhan

Reputation: 1

MYSQL to DB2 sql

Need some help to convert below MYSQL query to DB2 query:

SELECT FROM_UNIXTIME(CEILING((UNIX_TIMESTAMP(count_datetime))/300)*300) AS t,
sum(count_web) as web,
sum(count_mobile) as mobile,
sum(count_total) as total 
from clicks_user_count GROUP BY t  
ORDER BY `t`  DESC

Upvotes: 0

Views: 242

Answers (3)

Sirhan
Sirhan

Reputation: 1

Below SQL assisted by nfgl able to produce same result in DB2. Thx you.

SELECT timestamp(date(count_datetime)) + (midnight_seconds(count_datetime) / 300 * 300) seconds AS t, sum(count_web) as web, sum(count_mobile) as mobile, sum(count_total) as total from clicks_user_count GROUP BY timestamp(date(count_datetime)) + (midnight_seconds(count_datetime) / 300 * 300) seconds order by t

Upvotes: 0

nfgl
nfgl

Reputation: 3212

I thinks that's the equivalent to your query

SELECT
  timestamp(date(count_datetime)) + (midnight_seconds(count_datetime) / 300 * 300) seconds AS t,
  sum(count_web) as web,
  sum(count_mobile) as mobile,
  sum(count_total) as total
from clicks_user_count
GROUP BY timestamp(date(count_datetime)) + (midnight_seconds(count_datetime) / 300 * 300) seconds
order by t

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12314

CREATE OR REPLACE FUNCTION FROM_UNIXTIME (P_UTS BIGINT)
RETURNS TIMESTAMP
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN TIMESTAMP('1970-01-01-00.00.00') + CURRENT TIMEZONE + P_UTS SECONDS;

Upvotes: 1

Related Questions