将两个整数转换为时间戳

尝试这个：

WITH T (YYYYMMDD, HHMM) AS 
(
VALUES
  (20200304, 1046) -- return a timestamp for April 3, 2020, 10:46 AM.
, (20200304,  946) -- return a timestamp for April 3, 2020, 9:46 AM.
, (20200304, 2359) -- return a timestamp for April 3, 2020, 11:59 PM.
, (20200304,    0) -- return a timestamp for April 3, 2020, 0:00 AM.
)
SELECT 
  YYYYMMDD, HHMM
, TIMESTAMP_FORMAT(DIGITS(DEC(YYYYMMDD, 8)) || DIGITS(DEC(HHMM, 4)), 'YYYYMMDDHH24MI') AS TS 
FROM T;

结果是：

|YYYYMMDD   |HHMM       |TS                 |
|-----------|-----------|-------------------|
|20200304   |1046       |2020-03-04 10:46:00|
|20200304   |946        |2020-03-04 09:46:00|
|20200304   |2359       |2020-03-04 23:59:00|
|20200304   |0          |2020-03-04 00:00:00|

我设法找到了一个蛮力解决方案，但我仍然希望有更好的方法：

TIMESTAMP(
    FLOOR(MYDATE / 10000)
    CONCAT '-'
    CONCAT MOD(FLOOR(MYDATE / 100),100)
    CONCAT '-'
    CONCAT MOD(MYDATE, 100)
    CONCAT ' '
    CONCAT CASE
        WHEN MYTIME < 100 THEN '00:' CONCAT MYTIME
        ELSE FLOOR(MYTIME/100) CONCAT ':' CONCAT MOD(MYTIME,100)
    END
    CONCAT ':00'
)

另一种可能是

timestamp_format(
  substr(digits(mydate),3) || 
  substr(digits(mytime), 7), 'YYYYMMDDHH24MI');