在将基于十进制的时间戳转换为实际时间戳时使用HH24MISS扩展“格式-字符串”的问题

Question

tl;dr

为什么我不能转换下面的字符串时间戳

select timestamp_format('2015-08-21 000000', 'YYYY-MM-DD HH24MISS') as timestamp
  from sysibm.sysdummy1;

在i7.1.0/OS机器上？

尤其是因为我可以

select timestamp_format('000000' , 'HH24MISS') as timestamp
  from sysibm.sysdummy1;

至：

timestamp
-------------------------
2015-08-01 00:00:00.000000

上下文

在i7.1.0/OS机器上，我有一个表，表中的时间戳数据被分成几个十进制列，如

declare global temporary table tstamp
(
  year dec(4,0),
  month dec(2,0),
  day dec(2,0),
  time dec(6,0)
);

有这样的数据

insert into session.tstamp
  values (2015,8,21,92601),
         (2015,8,21,132601);

我想在上面做一些数据过滤。考虑到有些不灵活的格式，我认为最好将其转换为时间戳，并使用它查询表。所以我咨询了timestamp_format

我从建立日期部分开始，最后

select
  timestamp_format(YEAR || '-' || MONTH || '-' || DAY, 'YYYY-MM-DD') as timestamp
  from session.tstamp;

回传

TIMESTAMP
--------------------------
2015-08-21 00:00:00.000000  
2015-08-21 00:00:00.000000  

完美，让我们添加时间部分和显式lpad它包含六个字符：

select
  timestamp_format(YEAR || '-' || MONTH || '-' || DAY || ' ' || lpad(TIME, 6, '0'), 'YYYY-MM-DD HH24MISS') as timestamp
  from session.tstamp;

这将导致以下错误：

State: 22007 供应商代码：-20448 消息:使用为SQ20448指定的格式字符串，TIMESTAMP_FORMAT表达式无效。原因。。。。。：TIMESTAMP_FORMAT函数的参数1不能使用参数2中指定的格式字符串解释，原因如下之一：--字符串表达式太短或太长。-字符串表达式不符合格式字符串中指定的模板。--在字符串表达式中为格式字符串中的相应格式元素指定了太多位数。--字符串表达式中的值对格式字符串中的相应格式元素无效。恢复正常。。。指定函数的有效格式字符串。再试一次请求。

根据关于手册的format-string，字段之间的分隔符是可选的：

..。两个格式元素可以选择地由下列分隔符中的一个或多个字符分隔：

问题

那么，为什么我的值在使用‘YYYY DD HH24MISS’作为format-string时不被接受，因为我显式地将时间长度限制为6个字符？

旁注

单独使用HH24MISS作为format-string是可能的，因此我不能完全理解这一点。

select timestamp_format(lpad(TIME, 6, '0'), 'HH24MISS') as timestamp from session.tstamp;

TIMESTAMP
--------------------------
2015-08-01 13:26:01.000000  
2015-08-01 09:26:01.000000  

Answer 1

所描述的困难，是由于TIMESTAMP_FORMAT aka TO_DATE标量的缺陷造成的。测试显示的请求与预期的一样，使用IBMI7.3的DB2，并且如OP的注释所示，也在v7r2上运行。我曾问过一个类似的问题，“为什么失败用我先前的例子？”在SQL将文本mm/dd/yy转换为日期和时间戳中，但是我还没有重新访问更新版本中的这些示例。而且FWiW，IBMI7.1上可能有一些更新过的代码，用于该特性的最新代码；我没有这个级别的维护，所以我无法测试该版本的最后一次增强是否包括显然存在于新版本中的代码修复。

请注意，TO_DATE特性不是真正的内置功能，而是由系统提供的用户定义函数( UDF )，因此我个人建议采用另一种方法；即编写和使用特定于任务的标量UDF，并/或选择一种更兼容和更容易的方式，在定义这些列时从它们生成时间戳。假设所有日期都超过1000年的表达式如下，否则表达式必须更改为使用数字(年份)与仅使用年份

timestamp( YEAR concat digits( MONTH ) concat digits( DAY )
           concat digits( TIME )
         ) 

它的一个不同之处是使用算术实现14-字符时间戳-字符串形式'yyyymmddhhmmss‘的相同效果。

timestamp( concat( YEAR * 10000  + MONTH * 100 + DAY
                 , digits ( TIME ) ) )              

可以创建以下标量函数，以避免对视图查询或其他位置中的表达式进行编码。正如编码的那样，只在返回语句上使用表达式，应该允许内联；我没有指定任何其他可能与性能相关的子句，例如并行或on输入：

create function y4m2d2t6TS                   
( year   dec(4, 0)                           
, month  dec(2, 0)                           
, day    dec(2, 0)                           
, time   dec(6, 0)                           
) returns timestamp                          
language sql deterministic                   
return                                       
   digits( YEAR ) concat digits( MONTH )     
   concat digits( DAY ) concat digits( TIME )

; -- this semicolon is a statement separator, not terminator of above CREATE
select 
  y4m2d2t6TS( year, month, day, time ) as timestamp
from session.tstamp
; -- likeness of report from above query:
TIMESTAMP                 
2015-08-21-09.26.01.000000
2015-08-21-13.26.01.000000
********  End of data  ***

Answer 2

您可以在DB2中使用它：

values(VARCHAR_FORMAT(current_date,'YYYY-MM-DD HH24:MI:SS'))

再见