Mysql to Informix query

Question

我使用以下查询从mysql表中检索信息。

SELECT YEARWEEK(fecha,2) ,CONCAT('Semana ', WEEK(fecha)) as y,count(*) as a 
FROM mobile09 
WHERE fecha BETWEEN TODAY - 30 AND TODAY GROUP BY 1 ORDER BY 1 ASC

如何在具有相同表的Informix数据库中获得相同的结果？

Answer 1

您将必须为Informix实施自己的一周功能。

幸运的是，这已经完成了：IBM DeveloperWorks

CREATE FUNCTION day_one_week_one(yyyy INTEGER)
  RETURNING DATE
  WITH(NOT VARIANT)
  DEFINE jan1 DATE;
  LET jan1 = MDY(1, 1, yyyy);
  RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3;
END FUNCTION;
CREATE FUNCTION iso8601_weeknum(dateval DATE DEFAULT TODAY)
RETURNING CHAR(2)
WITH(NOT VARIANT)
    DEFINE rv CHAR(8);
    DEFINE yyyy CHAR(4);
    DEFINE ww CHAR(2);
    DEFINE d1w1 DATE;
    DEFINE tv DATE;
    DEFINE wn INTEGER;
    DEFINE yn INTEGER;
    -- Calculate year and week number.
    LET yn = YEAR(dateval);
    LET d1w1 = day_one_week_one(yn);
    IF dateval < d1w1 THEN
        -- Date is in early January and is in last week of prior year
        LET yn = yn - 1;
        LET d1w1 = day_one_week_one(yn);
    ELSE
        LET tv = day_one_week_one(yn + 1);
        IF dateval >= tv THEN
            -- Date is in late December and is in the first week of next year
            LET yn = yn + 1;
            LET d1w1 = tv;
        END IF;
    END IF;
    LET wn = TRUNC((dateval - d1w1) / 7) + 1;
    -- Calculation complete: yn is year number and wn is week number.
    -- Format result.
    LET yyyy = yn;
    IF wn < 10 THEN
        LET ww = "0" || wn;
    ELSE
        LET ww = wn;
    END IF
    RETURN ww;
END FUNCTION;

现在您应该能够做

select
   year(fecha) || iso8601_weeknum(fecha),
   'Semana ' || iso8601_weeknum(fecha) as y,
   count(*) as a
from
   mobile09
where
   fecha between today - 30 and today
group by
   1, 2
order by
   1 asc;