DB2 Convert ISO 8601 timestamp string to DB2 times

Question

I have a requirement where I get two strings in ISO 8601 Timestamp format and I have to compare them and get the max timestamp of them. The strings are in the following format.

2014-06-11T16:45:45Z

To compare, I need to cast them into DB2 timestamp and then compare. The problem is with the "T" and "Z" letters. Because of that, I am unable to cast. I know that I can simply REPLACE the T and Z and cast, but I wanted to know if there is a better way.

I tried the following functions but was not able to get the desired results.

to_date, to_timestamp, varchar_format, cast as

Using DB2 LUW v9.7

Answer 1

First, the good news: your values are SARGable, that is simply comparing them as strings will return the correct results (MAX(...) will work as needed). This won't help with the casting, but at least it'd still spit out the "greater" value.

For casting, there's a couple of things you could do here.

First, so long as your date/time portions remain in that format, it's probably easier to grab them separately and recombine for the timestamp:

TIMESTAMP(SUBSTR(@inputParm, 1, 10), SUBSTR(@inputParm, 12, 8)) AS resultTimestamp

LUW also has a function called TIMESTAMP_FORMAT (TO_TIMESTAMP is technically a synonym for this). I'm assuming that the system is actually choking on the fact that you don't have fractional seconds in your incoming data. I'd recommend trying something like this:

TIMESTAMP_FORMAT('YYYY-MM-DD HH24:MI:SS ', @inputParm)

The better option, however, may be to get whoever's calling your db to pass in a parameter typed as a timestamp instead of as a string - this means you wouldn't have to do any conversion workarounds at all.

Answer 2

Apart from the fact that my values are Sargable (as mentioned by Clockwork-Muse), I still went ahead and wrote code to Unbox (ISO 8601 String to Timestamp) and Box (Timestamp to ISO 8601 String) the values. Once unboxed, I compared them and once I got the max value, I boxed it.

Unboxing (Used REPLACE and TO_TIMESTAMP function):

SELECT 
   TO_TIMESTAMP(REPLACE(REPLACE('2014-04-14T15:19:45Z','T',' '),'Z',''),'YYYY-MM-DD HH24:MI:SS')
FROM SYSIBM.SYSDUMMY1;

Boxing (Unboxing appends an extra .0 microseconds, so replacing it as well)

SELECT REPLACE(REPLACE('2014-04-14 15:19:45.0',' ','T'),'.0','Z')
FROM SYSIBM.SYSDUMMY1