Round timestamps up to a whole hour in Informix SQL

Question

I have a database table that has a column called dlv_tm which contains timestamps with the format of hh:mm:ss

Most of the timestamps are whole hours, e.g. 09:00:00, 11:00:00, etc., but some are intentionally offset by one minute, e.g. 08:59:00, 10:59:00, etc. to differentiate them for various reasons.

However, this means that pulling data from this table gives me very diffuse results, and I would like to aggregate the data ignoring those offsets, i.e. treat records with that one minute offset as if the offset wasn't there.

In other words, when coming across a record with a time stamp of 08:59:00, I would like to pull it with a time stamp of 09:00:00.

I've tried different approaches using a CASE/WHEN statement and datediff to determine whether I should add a minute to the timestamp or not, which I did not get to work, or ROUND, which didn't produce the desired results which I think has something to do with the hh:mm:ss format being a non-standard date/time format.

Any tips/suggestions?

Answer 1

Using Informix 12.10FC8DE I can use the following:

CREATE TABLE my_dlv_tm
(
    id INTEGER
    , tmstmp DATETIME HOUR TO SECOND
);

INSERT INTO my_dlv_tm VALUES (1, '09:00:00');
INSERT INTO my_dlv_tm VALUES (2, '08:59:00');
INSERT INTO my_dlv_tm VALUES (3, '09:01:00');
INSERT INTO my_dlv_tm VALUES (4, '00:00:00');
INSERT INTO my_dlv_tm VALUES (5, '23:59:00');
INSERT INTO my_dlv_tm VALUES (6, '00:01:00');

On the example table I use the following statement:

SELECT
    id
    , tmstmp
    , ROUND(tmstmp::DATETIME YEAR TO SECOND, 'HH')::DATETIME HOUR TO SECOND AS rounded_tmstmp
FROM
    my_dlv_tm;


 id tmstmp   rounded_tmstmp

  1 09:00:00 09:00:00
  2 08:59:00 09:00:00
  3 09:01:00 09:00:00
  4 00:00:00 00:00:00
  5 23:59:00 00:00:00
  6 00:01:00 00:00:00

6 row(s) retrieved.

Directly using a DATETIME HOUR TO SECOND produces the following error:

1263: A field in a datetime or interval value is incorrect or an illegal operation

So I cast DATETIME HOUR TO SECOND to DATETIME YEAR TO SECOND, use the ROUND function and finally cast the result back to DATETIME HOUR TO SECOND.

I am not sure why it needs the casts, from what I read at IBM® Informix® 12.10 ROUND Function I did not understand that the DATETIME argument for ROUND needs a date component.

Answer 2

Here's an alternative way to approach it. I worked in 2 steps (a form of TDQD — Test-Driven Query Design). A major problem is that adding 30 minutes to a DATETIME HOUR TO SECOND value such as 23:59:00 overflows the range of acceptable values for DATETIME HOUR TO SECOND.

Step 1: Using DATETIME YEAR TO SECOND

+ CREATE TABLE round_datetime
(
    id SERIAL NOT NULL PRIMARY KEY,
    dt DATETIME YEAR TO SECOND NOT NULL
);
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 09:00:00');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 08:59:00');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 09:01:00');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 00:00:00');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 23:29:59');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 23:30:00');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 23:59:00');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 00:01:00');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 00:29:59');
+ INSERT INTO round_datetime VALUES (0, '2017-12-18 00:30:00');
+ SELECT id,
       dt,
       EXTEND(EXTEND(dt + 30 UNITS MINUTE, YEAR TO HOUR), YEAR TO SECOND)
  FROM round_datetime
 ORDER BY dt;

SERIAL  DATETIME YEAR TO SECOND DATETIME YEAR TO SECOND
4       2017-12-18 00:00:00     2017-12-18 00:00:00
8       2017-12-18 00:01:00     2017-12-18 00:00:00
9       2017-12-18 00:29:59     2017-12-18 00:00:00
10      2017-12-18 00:30:00     2017-12-18 01:00:00
2       2017-12-18 08:59:00     2017-12-18 09:00:00
1       2017-12-18 09:00:00     2017-12-18 09:00:00
3       2017-12-18 09:01:00     2017-12-18 09:00:00
5       2017-12-18 23:29:59     2017-12-18 23:00:00
6       2017-12-18 23:30:00     2017-12-19 00:00:00
7       2017-12-18 23:59:00     2017-12-19 00:00:00

It may be simpler if the expression EXTEND(EXTEND(dt + 30 UNITS MINUTE, YEAR TO HOUR), YEAR TO SECOND) is broken down in stages, which is how it was developed:

dt + 30 UNITS MINUTE

This adds 30 minutes to each time, happily wrapping to the early part of the next day.

EXTEND(dt + 30 UNITS MINUTE, YEAR TO  HOUR

This removes the MINUTES and SECONDS, truncating the values (which is why the 30 minutes was added).

EXTEND(EXTEND(dt + 30 UNITS MINUTE, YEAR TO  HOUR), YEAR TO SECOND)

This reinstates the MINUTES and SECONDS, but sets them to zero. The double EXTEND operation is crucial. Trying to use just EXTEND(dt + 30 UNITS MINUTE, HOUR TO SECOND), for example, doesn't zero the minutes and seconds components of the answer.

Step 2: Using DATETIME HOUR TO SECOND

This is closely based on the previous step, but exploits the fact that when you extend a DATETIME HOUR TO SECOND value with YEAR to DAY components, they take the values corresponding to the current date. Thus:

+ CREATE TABLE round_time
(
    id SERIAL NOT NULL PRIMARY KEY,
    dt DATETIME HOUR TO SECOND NOT NULL
);
+ INSERT INTO round_time VALUES (0, '09:00:00');
+ INSERT INTO round_time VALUES (0, '08:59:00');
+ INSERT INTO round_time VALUES (0, '09:01:00');
+ INSERT INTO round_time VALUES (0, '00:00:00');
+ INSERT INTO round_time VALUES (0, '23:29:59');
+ INSERT INTO round_time VALUES (0, '23:30:00');
+ INSERT INTO round_time VALUES (0, '23:59:00');
+ INSERT INTO round_time VALUES (0, '00:01:00');
+ INSERT INTO round_time VALUES (0, '00:29:59');
+ INSERT INTO round_time VALUES (0, '00:30:00');
+ SELECT id,
       dt,
       EXTEND(dt, YEAR TO SECOND),
       EXTEND(EXTEND(EXTEND(dt, YEAR TO SECOND) + 30 UNITS MINUTE, YEAR TO HOUR), YEAR TO SECOND),
       EXTEND(EXTEND(EXTEND(dt, YEAR TO SECOND) + 30 UNITS MINUTE, YEAR TO HOUR), HOUR TO SECOND)
  FROM round_time
 ORDER BY dt;
SERIAL  DATETIME HOUR TO SECOND DATETIME YEAR TO SECOND DATETIME YEAR TO SECOND DATETIME HOUR TO SECOND
4       00:00:00        2017-12-16 00:00:00     2017-12-16 00:00:00     00:00:00
8       00:01:00        2017-12-16 00:01:00     2017-12-16 00:00:00     00:00:00
9       00:29:59        2017-12-16 00:29:59     2017-12-16 00:00:00     00:00:00
10      00:30:00        2017-12-16 00:30:00     2017-12-16 01:00:00     01:00:00
2       08:59:00        2017-12-16 08:59:00     2017-12-16 09:00:00     09:00:00
1       09:00:00        2017-12-16 09:00:00     2017-12-16 09:00:00     09:00:00
3       09:01:00        2017-12-16 09:01:00     2017-12-16 09:00:00     09:00:00
5       23:29:59        2017-12-16 23:29:59     2017-12-16 23:00:00     23:00:00
6       23:30:00        2017-12-16 23:30:00     2017-12-17 00:00:00     00:00:00
7       23:59:00        2017-12-16 23:59:00     2017-12-17 00:00:00     00:00:00

The final column seems likely to be the answer you want. Clearly, if there is a suitable DATE column in the table already, that could be used instead, especially if you want the date to be part of the analysis. Then you would use some variation on the Step 1 SQL to produce the appropriate result.

As with all date/time computations (at least in Informix), the expressions are wordy.