SQL-将24小时（``法定''）时间（2145）转换为``M / PM时间''（9:45 pm）

Question

我有2个正在使用的字段存储为smallint军事结构化时间。
编辑 我正在IBM Informix Dynamic Server版本10.00.FC9上运行

beg_tm和end_tm

样本值

beg_tm   545
end_tm   815

beg_tm   1245
end_tm   1330

样品输出

beg_tm   5:45 am
end_tm   8:15 am

beg_tm   12:45 pm
end_tm   1:30 pm

我曾在Perl中工作过，但我正在寻找一种使用SQL和case语句的方法。

这有可能吗？

编辑

本质上，此格式必须在ACE报告中使用。我找不到使用以下简单块在输出部分中对其进行格式化的方法

if(beg_tm>=1300) then
beg_tm = vbeg_tm - 1200

其中vbeg_tm是声明的char（4）变量

编辑 这适用于小时数> = 1300（2230除外！）

select substr((beg_tm-1200),0,1)||":"||substr((beg_tm-1200),2,2) from mtg_rec where beg_tm>=1300;

这可以在<1200小时内运行（有时。。。。10:40失败了）

select substr((mtg_rec.beg_tm),0,(length(cast(beg_tm as varchar(4)))-2))||":"||(substr((mtg_rec.beg_tm),2,2))||" am" beg_tm from mtg_rec where mtg_no = 1;

编辑
Jonathan Leffler的表达方式中使用的强制转换语法

SELECT  beg_tm,
        cast((MOD(beg_tm/100 + 11, 12) + 1) as VARCHAR(2)) || ':' ||
        SUBSTRING(cast((MOD(beg_tm, 100) + 100) as CHAR(3)) FROM 2) ||
        SUBSTRING(' am pm' FROM (MOD(cast((beg_tm/1200) as INT), 2) * 3) + 1 FOR 3),
        end_tm,
        cast((MOD(end_tm/100 + 11, 12) + 1) as VARCHAR(2)) || ':' ||
        SUBSTRING(cast((MOD(end_tm, 100) + 100) as CHAR(3)) FROM 2) ||
        SUBSTRING(' am pm' FROM (MOD(cast((end_tm/1200) as INT), 2) * 3) + 1 FOR 3)
      FROM mtg_rec
      where mtg_no = 39;

Answer 1

请注意，SO 440061提供了有关在12小时制和24小时制之间转换时间的有用信息（与该转换相反）。这并非微不足道，因为上午12:45到凌​​晨1:15才半小时。

接下来，请注意Informix（IDS Informix Dynamic Server）7.31版最终于2009-09-30停止服务;
它不再是受支持的产品。

您应该更精确地使用您的版本号；例如，在7.30.UC1和7.31.UD8之间有相当大的差异。

但是，您应该能够使用TO_CHAR（）函数根据需要设置时间格式。尽管此引用是针对IDS
12.10信息中心的，但我相信您将能够在7.31中使用它（不一定在7.30中使用它，但是您在过去十年的大部分时间里都不会使用它）。

它说，有一个’％R’格式说明符可以持续24小时。它还将您引向‘
GL_DATETIME
‘，其中说’％I’给您12小时的时间，’％p’给您am / pm指示器。我还找到了IDS的7.31.UD8实例来验证这一点：

select to_char(datetime(2009-01-01 16:15:14) year to second, '%I:%M %p')
    from dual;

04:15 PM

select to_char(datetime(2009-01-01 16:15:14) year to second, '%1.1I:%M %p')
    from dual;

4:15 PM

通过重新阅读问题，我看到您实际上具有0000..2359范围内的SMALLINT值，需要进行转换。通常，我会指出Informix具有用于存储此类值的类型-
DATETIME HOUR TO MINUTE-但我承认它在磁盘上占用3个字节，而不是2个字节，因此它不像SMALLINT表示法那么紧凑。

史蒂夫·卡斯（Steve Kass）显示了SQL Server表示法：

select
  cast((@milTime/100+11)%12+1 as varchar(2))
 +':'
 +substring(cast((@milTime%100+100) as char(3)),2,2)
 +' '
 +substring('ap',@milTime/1200%2+1,1)
 +'m';

正确设置小时数的技巧很巧妙-感谢Steve！

假定表为：转换为IDS 11.50的Informix。

CREATE TEMP TABLE times(begin_tm SMALLINT NOT NULL);

SELECT  begin_tm,
        (MOD(begin_tm/100 + 11, 12) + 1)::VARCHAR(2) || ':' ||
        SUBSTRING((MOD(begin_tm, 100) + 100)::CHAR(3) FROM 2) || ' ' ||
        SUBSTRING("ampm" FROM (MOD((begin_tm/1200)::INT, 2) * 2) + 1 FOR 2)
      FROM times
      ORDER BY begin_tm;

使用FROM和FOR的SUBSTRING表示法是标准的SQL表示法-很奇怪，但是如此。

结果示例：

     0    12:00 am 
     1    12:01 am 
    59    12:59 am 
   100    1:00 am  
   559    5:59 am  
   600    6:00 am  
   601    6:01 am  
   959    9:59 am  
  1000    10:00 am 
  1159    11:59 am 
  1200    12:00 pm 
  1201    12:01 pm 
  1259    12:59 pm 
  1300    1:00 pm  
  2159    9:59 pm  
  2200    10:00 pm 
  2359    11:59 pm 
  2400    12:00 am

注意：值559-601在列表中，因为在没有强制转换为整数的情况下，我遇到了舍入而不是截断的问题。

现在，已在IDS 11.50上对其进行了测试；IDS 7.3x将没有强制转换符号。但是，这不是问题。接下来的评论将要解决…

作为一种如何在不使用条件语句等条件的情况下用SQL编写表达式的练习，这很有趣，但是如果有人在整个套件中多次编写该表达式，我会射击它们，因为它们缺乏模块化。显然，这需要一个存储过程-
并且一个存储过程不需要（显式）强制转换或其他一些技巧，尽管这些赋值强制执行隐式强制转换：

CREATE PROCEDURE ampm_time(tm SMALLINT) RETURNING CHAR(8);
    DEFINE hh SMALLINT;
    DEFINE mm SMALLINT;
    DEFINE am SMALLINT;
    DEFINE m3 CHAR(3);
    DEFINE a3 CHAR(3);
    LET hh = MOD(tm / 100 + 11, 12) + 1;
    LET mm = MOD(tm, 100) + 100;
    LET am = MOD(tm / 1200, 2);
    LET m3 = mm;
    IF am = 0
    THEN LET a3 = ' am';
    ELSE LET a3 = ' pm';
    END IF;
    RETURN (hh || ':' || m3[2,3] || a3);
END PROCEDURE;

Informix’[2,3]’表示法是子字符串运算符的原始形式。之所以是原始的，是因为（出于某些原因，我还是无法理解）下标必须是文字整数（不是变量，不是表达式）。它恰好在这里有用。总的来说，这令人沮丧。

该存储过程可以在您可以使用的任何Informix版本（Online 5.x，SE 7.x，IDS 7.x或9.x，10.00、11.x，12.x）上运行。

为了说明等价的表达式和存储过程：

SELECT  begin_tm,
        (MOD(begin_tm/100 + 11, 12) + 1)::VARCHAR(2) || ':' ||
        SUBSTRING((MOD(begin_tm, 100) + 100)::CHAR(3) FROM 2) ||
        SUBSTRING(' am pm' FROM (MOD((begin_tm/1200)::INT, 2) * 3) + 1 FOR 3),
        ampm_time(begin_tm)
      FROM times
      ORDER BY begin_tm;

产生结果：

     0  12:00 am        12:00 am
     1  12:01 am        12:01 am
    59  12:59 am        12:59 am
   100  1:00 am         1:00 am 
   559  5:59 am         5:59 am 
   600  6:00 am         6:00 pm 
   601  6:01 am         6:01 pm 
   959  9:59 am         9:59 pm 
  1000  10:00 am        10:00 pm
  1159  11:59 am        11:59 pm
  1200  12:00 pm        12:00 pm
  1201  12:01 pm        12:01 pm
  1259  12:59 pm        12:59 pm
  1300  1:00 pm         1:00 pm 
  2159  9:59 pm         9:59 pm 
  2200  10:00 pm        10:00 pm
  2359  11:59 pm        11:59 pm
  2400  12:00 am        12:00 am

现在，可以在ACE报告中的单个SELECT语句中多次使用此存储过程，而无需费劲。

[ 在原始海报上发表有关无法正常工作的评论后… ]

IDS 7.31不处理传递给MOD（）函数的非整数值。因此，除法必须存储在显式整数变量中-因此：

CREATE PROCEDURE ampm_time(tm SMALLINT) RETURNING CHAR(8);
    DEFINE i2 SMALLINT;
    DEFINE hh SMALLINT;
    DEFINE mm SMALLINT;
    DEFINE am SMALLINT;
    DEFINE m3 CHAR(3);
    DEFINE a3 CHAR(3);
    LET i2 = tm / 100;
    LET hh = MOD(i2 + 11, 12) + 1;
    LET mm = MOD(tm, 100) + 100;
    LET i2 = tm / 1200;
    LET am = MOD(i2, 2);
    LET m3 = mm;
    IF am = 0
    THEN LET a3 = ' am';
    ELSE LET a3 = ' pm';
    END IF;
    RETURN (hh || ':' || m3[2,3] || a3);
END PROCEDURE;

这已在Solaris 10上的IDS 7.31.UD8上进行了测试，并且可以正常工作。我不了解所报告的语法错误；但是存在版本依赖性的外部机会-
为了以防万一，报告版本号和平台 始终 至关重要。注意，我小心地记录了各种事情在哪里工作。这不是偶然，也不是大惊小怪-它基于多年的经验。