DB2将ISO 8601时间戳字符串转换为DB2时间戳 [英] DB2 Convert ISO 8601 timestamp string to DB2 timestamp

问题描述

我有一个要求，我得到两个字符串ISO 8601时间戳格式，我必须比较它们，并获得他们的最大时间戳。字符串格式如下。

  2014-06-11T16：45：45Z 
  

为了比较，我需要将它们转换成DB2时间戳，然后进行比较。问题是T和Z字母。因此，我无法投下。我知道我可以简单地 REPLACE T和Z并投出，但我想知道是否有更好的方法。

我尝试过以下功能，但无法获得所需的结果。

  to_date，to_timestamp，varchar_format，cast作为
  

使用DB2 LUW v9.7

解决方案

首先，好消息：您的价值观是可接受的，只是将其作为字符串比较将返回正确的结果（ MAX ...）将根据需要工作）。这不会有助于投射，但至少它仍然会抛出更大的价值。

对于投射，你可以做几件事

首先，只要您的日期/时间部分保留在该格式中，可能更容易单独抓取它们并重新组合时间戳记：

  TIMESTAMP（SUBSTR（@inputParm，1，10），SUBSTR（@inputParm，12,8））AS resultTimestamp 
  

LUW还具有一个名为 TIMESTAMP_FORMAT （ TO_TIMESTAMP 在技术上这个的同义词）。我假设系统实际上窒息了你在传入数据中没有小数秒的事实。我建议尝试这样的事情：

  TIMESTAMP_FORMAT（'YYYY-MM-DD HH24：MI：SS'，@inputParm ）
  

然而，更好的选择可能是获取谁呼叫您的数据库传递参数的类型作为时间戳而不是字符串 - 这意味着您根本不必进行任何转换解决方法。

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

解决方案

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.

这篇关于DB2将ISO 8601时间戳字符串转换为DB2时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！