CODEWITHSUNDEEP
sqldatetimeinformix
Erns Bauer
Erns Bauer

Reputation: 15

DATETIME - DATETIME in HH:MM:SS format from Informix

I have 2 fields (login,logout) INTEGER type with the UNIX time, but when I make a subtraction (logout-login) using DBINFO it returns: 0 05:50:23, but I need that value without the first number (zero in this case).

I am using Informix 11.

This is the expression I use:

DBINFO('utc_to_datetime', logout) -
DBINFO('utc_to_datetime', login)

The result:

0 05:50:23

How can I get rid of the leading zero?

Upvotes: 1

Views: 3073

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753645

The difference between two DATETIME YEAR TO SECOND values is an INTERVAL DAY(n) TO SECOND value by default (n = 8), and the leading 0 tells you that there are 0 days (plus the 5 hours, etc) in the difference between the times. It appears that you'd rather have an INTERVAL HOUR(9) TO SECOND result, so you need to ask for it:

SELECT CAST(DBINFO('utc_to_datetime', logout) -
            DBINFO('utc_to_datetime', login) AS INTERVAL HOUR(9) TO SECOND)
  FROM AnonymousTableWithColsLoginAndLogout

Test:

CREATE TABLE AnonymousTableWithColsLoginAndLogout
(
    login INTEGER NOT NULL,
    logout INTEGER NOT NULL
);
INSERT INTO AnonymousTableWithColsLoginAndLogout VALUES(1473961283, 1473961283 + (5 * 60 + 50) * 60 + 23);
SELECT CAST(DBINFO('utc_to_datetime', logout) -
            DBINFO('utc_to_datetime', login) AS INTERVAL HOUR(9) TO SECOND)
      FROM AnonymousTableWithColsLoginAndLogout
;

Result:

5:50:23

The key is knowing the data type returned from the difference, and knowing how to cast that to a type that you want.

Note that if there are 3 days, 5 hours, 50 minutes, 23 seconds difference, then the result will be shown as 77:50:23.

Incidentally, you could avoid using DBINFO by writing:

SELECT CAST((logout - login) UNITS SECOND AS INTERVAL HOUR(9) TO SECOND)
  From AnonymousTableWithColsLoginAndLogout;

It produces the same answer.

Is there a way to have this format: HH:MM:SS. I mean, in the example you gave me, is there a way to add a zero before number 5?

Not sure, but probably. One issue is 'which language are you collecting the results in'? Or 'how are you converting the INTERVAL DAY(9) TO SECOND into a string'? It may affect the options. I was using my SQLCMD — a DB-Access work-a-bit-alike-but-better program. If you need to do it in a similar environment, rather than perhaps C or Java or C# or …, then you could write ghastligrams which check whether there's a single leading digit in the string and add a zero if not. There's also a trick:

SELECT EXTEND(DATETIME(2000-01-01 00:00:00) YEAR TO SECOND +
       CAST((logout - login) UNITS SECOND AS INTERVAL HOUR(9) TO SECOND), HOUR TO SECOND)
  From AnonymousTableWithColsLoginAndLogout;

On the previous sample data, that produces the desired result:

05:50:23

The date specified in the DATETIME literal is arbitrary; the key is to use 'midnight'. The result is now a DATETIME HOUR TO SECOND instead of INTERVAL HOUR(9) TO SECOND or similar. This means that if you have an interval of 24 hours or more, you are not going to get a good representation of the data because it will produce an answer modulo 24 hours. The addition will succeed, but the information about the extra days will be thrown away. For intervals of less than 24 hours, it will be fine.

You might need to look up whether the TO_CHAR() function provides sufficient format control when formatting an INTERVAL (indeed, you'll need to check whether it accepts an interval type at all, and what it does if given one).

Upvotes: 3

Related Questions