SQL - 将 24 小时 ("military") 时间 (2145) 转换为 "AM/PM time"(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 报告中使用。我找不到使用简单 block 在输出部分中格式化它的方法

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 年 9 月 30 日终止服务；它不再是受支持的产品。

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

但是，您应该可以使用 TO_CHAR()根据需要格式化时间。虽然此引用是针对 IDS 12.10 Information Center ，我相信你将能够在 7.31 中使用它(不一定在 7.30 中，但在过去十年的大部分时间里你不应该使用它)。

它说，有一个 24 小时制的“%R”格式说明符。它还向您推荐' GL_DATETIME '，其中 '%I' 为您提供 12 小时时间，而 '%p' 为您提供上午/下午指示符。我还找到了一个 7.31.UD8 的 IDS 实例来验证这一点:

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

我从重新阅读问题中看到，您实际上有 SMALLINT 值在 0000..2359 范围内，并且需要将它们转换。通常，我会指出 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';

正确计时的诀窍很巧妙 - 谢谢史蒂夫!

转换为 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 上进行了测试，并且工作正常。我不明白报告的语法错误；但是存在版本依赖性的外部机会 - 报告版本号和平台以防万一总是至关重要的。请注意，我很小心地记录了各种工作的位置；这不是偶然的，也不是大惊小怪的——这是基于多年的经验。