CODEWITHSUNDEEP
informix
Conrad Jagger
Conrad Jagger

Reputation: 153

Informix - Date/Time Manipulation in where clause

See below query:

DROP TABLE IF EXISTS rd_rt_date_integer;
CREATE TABLE rd_rt_date_integer
(
    run_date    DATE NOT NULL,
    run_time    INTEGER NOT NULL
                CHECK (run_time >= 0 AND run_time < 2400 AND MOD(run_time, 100) < 60),
    PRIMARY KEY(run_date, run_time)
);

INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 0);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 100);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 200);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 300);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 400);

SELECT run_date, run_time,
       EXTEND(run_date, YEAR TO MINUTE) +
       MOD(run_time, 100) UNITS MINUTE +
       (run_time / 100) UNITS HOUR AS run_date_time
  FROM rd_rt_date_integer;

Question: How can we apply condition in where to clause to fetch data from certain time onwards

SELECT run_date, run_time,
       EXTEND(run_date, YEAR TO MINUTE) +
       MOD(run_time, 100) UNITS MINUTE +
       (run_time / 100) UNITS HOUR AS run_date_time
  FROM rd_rt_date_integer
where EXTEND(run_date, YEAR TO MINUTE) +
       MOD(run_time, 100) UNITS MINUTE +
       (run_time / 100) UNITS HOUR >='2017-05-22 02:00'

I just want to understand what is the best way to do manipulation in where clause itself where im concatenating run_date and run_time...

Upvotes: 0

Views: 2951

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753645

It would be simpler if you stored the time as an INTERVAL HOUR TO MINUTE. Then you could simplify the first query by using:

DROP TABLE IF EXISTS rd_rt_date_integer;
CREATE TABLE rd_rt_date_integer
(
    run_date    DATE NOT NULL,
    run_time    INTERVAL HOUR TO MINUTE NOT NULL
                CHECK (run_time >= INTERVAL(0:0) HOUR TO MINUTE AND run_time < INTERVAL(24:00) HOUR TO MINUTE),
    PRIMARY KEY(run_date, run_time)
);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', '0:0');
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', '1:00');
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', '2:00');
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', '3:00');
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', '4:00');

SELECT run_date, run_time,
       EXTEND(run_date, YEAR TO MINUTE) + run_time AS run_date_time
  FROM rd_rt_date_integer;

However, you presumably have a reason for using the integer run_time, even though it makes time calculations hellish.

This code works — I recommend using the stored procedure:

DROP TABLE IF EXISTS rd_rt_date_integer;
CREATE TABLE rd_rt_date_integer
(
    run_date    DATE NOT NULL,
    run_time    INTEGER NOT NULL
                CHECK (run_time >= 0 AND run_time < 2400 AND MOD(run_time, 100) < 60),
    PRIMARY KEY(run_date, run_time)
);

INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 0);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 100);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 200);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 300);
INSERT INTO rd_rt_date_integer VALUES('2017-05-22', 400);

SELECT run_date, run_time,
       EXTEND(run_date, YEAR TO MINUTE) +
       MOD(run_time, 100) UNITS MINUTE +
       (run_time / 100) UNITS HOUR AS run_date_time
  FROM rd_rt_date_integer
 WHERE EXTEND(run_date, YEAR TO MINUTE) +
       MOD(run_time, 100) UNITS MINUTE +
       (run_time / 100) UNITS HOUR >= DATETIME(2017-05-22 02:00) YEAR TO MINUTE
    OR EXTEND(run_date, YEAR TO MINUTE) +
       MOD(run_time, 100) UNITS MINUTE +
       (run_time / 100) UNITS HOUR >= EXTEND('2017-05-22 02:00', YEAR TO MINUTE)
;

DROP FUNCTION IF EXISTS run_date_time;

CREATE FUNCTION run_date_time(rd DATE, rt INTEGER)
    RETURNING DATETIME YEAR TO MINUTE;
    DEFINE rv DATETIME YEAR TO MINUTE;
    LET rv = EXTEND(rd, YEAR TO MINUTE) + MOD(rt, 100) UNITS MINUTE  + (rt / 100) UNITS HOUR;
    RETURN rv;
END FUNCTION;

SELECT run_date, run_time,
       run_date_time(run_date, run_time) AS run_date_time
  FROM rd_rt_date_integer
 WHERE run_date_time(run_date, run_time) >= DATETIME(2017-05-22 02:00) YEAR TO MINUTE
    OR run_date_time(run_date, run_time) >= EXTEND('2017-05-22 02:00', YEAR TO MINUTE)
    OR run_date_time(run_date, run_time) >= run_date_time('2017-05-22', 200)
;

The OR conditions in the later SELECT statements show different nomenclatures for writing the condition. They're otherwise identical and only one of the conditions is needed; the others are superfluous.

If you simply stored the minutes since midnight in the run_time column, rather than encoding it as 100 * hours + minutes, then you could write expressions like:

DATETIME(2017-06-12 00:00) YEAR TO MINUTE + 245 UNITS MINUTE

That expression evaluates to 2017-06-12 04:05. You could easily arrange to update the table to this encoding. There are obvious variants of this, such as:

EXTEND(TODAY, YEAR TO MINUTE) + 245 UNITS MINUTE
EXTEND(run_date, YEAR TO MINUTE) + run_time UNITS MINUTE

Upvotes: 1

Related Questions