代码之家 › 专栏 › 技术社区 › CheeseConQueso

SQL-将24小时(“军事”)时间(2145)转换为“上午/下午时间”(晚上9:45)

informix formatting datetime sql

CheeseConQueso · 技术社区 · 16 年前

我有两个正在使用的字段,它们存储为smallint军用结构化时间。
编辑我运行的是IBM Informix动态服务器版本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;

7 回复 | 直到 16 年前

Jonathan Leffler 8 年前

请注意,网站上有有用的信息 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”表示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

通过重读这个问题,我发现实际上,SMALLINT值在0000..2359范围内,需要对其进行转换。通常,我会指出Informix有一种存储此类值的类型—DATETIME小时到分钟—但我承认它在磁盘上占用了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';

转换为Informix for IDS 11.50,假设该表为:

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;

示例结果:

     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

现在,这是在IDS11.50上测试的;IDS7.3x没有强制转换符号。然而,这不是问题;下一个评论是关于。。。

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]”符号是子字符串运算符的原始形式;原语,因为(出于我仍然无法理解的原因)下标必须是文字整数(不是变量,不是表达式)。它正好在这里有用;总的来说,这是令人沮丧的。

要说明表达式和存储过程(上的次要变量)的等价性,请执行以下操作:

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语句中多次使用,无需进一步ado。

在原始海报上关于不工作的评论之后。。。 ]

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上测试的,并且工作正常。我不理解报告的语法错误;但是存在版本依赖的外部可能性——确实如此总是

Steve Kass 16 年前

mjv的第二次尝试仍然不起作用。(例如,对于0001,它给出0:1 am。)

这里有一个T-SQL解决方案,它应该工作得更好。它可以通过使用适当的语法进行连接和子串来适应其他方言。

它也适用于军事时间2400(上午12:00),这可能是有用的。

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';

mjv 16 年前

未经测试 史蒂夫·卡斯港的解决方案 Informix .

Steve的解决方案本身在MS SQL Server下经过了良好的测试。与以前的解决方案相比,我更喜欢它,因为 转换为am/pm时间仅以代数方式完成 不需要任何人的帮助分支

如果数字“军事时间”来自数据库,则用列名替换@milTime。@变量仅用于测试。

--declare @milTime int
--set @milTime = 1359
SELECT
  CAST(MOD((@milTime /100 + 11), 12) + 1 AS VARCHAR(2))
  ||':'
  ||SUBSTRING(CAST((@milTime%100 + 100) AS CHAR(3)) FROM 2 FOR 2)
  ||' '
  || SUBSTRING('ap' FROM (MOD(@milTime / 1200, 2) + 1) FOR 1)
  || 'm';

以下是我的[fixed],基于案例的SQL Server解决方案,仅供参考

SELECT 
  CASE ((@milTime / 100) % 12)
      WHEN 0 THEN '12'
      ELSE CAST((@milTime % 1200) / 100 AS varchar(2))
  END 
  + ':' + RIGHT('0' + CAST((@milTime % 100) AS varchar(2)), 2)
  + CASE (@milTime / 1200) WHEN 0 THEN ' am' ELSE ' pm' END

user305368 user305368 16 年前

啊,Jenzabar的一位用户(Jonathan,不要对模式太残酷,它们已经有几十年的历史了)。很惊讶你没有在CX技术列表上问这个问题。我已经向您发送了一个用于CX的RCS就绪存储过程。

-西南

{
 Revision Information (Automatically maintained by 'make' - DON'T CHANGE)
 -------------------------------------------------------------------------
 $Header$
 -------------------------------------------------------------------------
}
procedure       se_get_inttime
privilege       owner
description     "Get time from an integer field and return as datetime"
inputs          param_time integer      "Integer formatted time"
returns         datetime hour to minute "Time in datetime format"
notes           "Get time from an integer field and return as datetime"

begin procedure

DEFINE tm_str VARCHAR(255);
DEFINE h INTEGER;
DEFINE m INTEGER;

IF (param_time < 0 OR param_time > 2359) THEN
RAISE EXCEPTION -746, 0, "Invalid time format. Should be: 0 - 2359";
END IF

LET tm_str = LPAD(param_time, 4, 0);

LET h = SUBSTR(tm_str, 1, 2);

IF (h < 0 OR h > 23) THEN
RAISE EXCEPTION -746, 0, "Invalid time format. Should be: 0 - 2359";
END IF

LET m = SUBSTR(tm_str, 3, 4);

IF (m < 0 OR m > 59) THEN
RAISE EXCEPTION -746, 0, "Invalid time format. Should be: 0 - 2359";
END IF

RETURN TO_DATE(h || ':' || m , '%R');

end procedure

grant
    execute to (group public)

Thorsten 16 年前

对于informix不太确定,下面是我在Oracle中要做的事情(一些示例,但由于我在家里没有经过测试):

将整数转换为字符串: To_Char (milTime) ,例如1->'1',545->'545',1215->'1215'
请确保始终使用四个字符的字符串: Right('0000'||To_Char(milTime), 4) ,例如1->'0001',545->'0545',1215->'1215'
变成日期时间: To_Date (Right('0000'||To_Char(milTime), 4), 'HH24:MI')
输出到所需格式: To_Char(To_Date(..),'HH:MI AM') e、 g.1->'上午00:01',545->'凌晨5时45分,1215->'下午12时15分'

Oracle的To_Date和To_Char是专有的,但我确信有一些标准的SQL或Informix函数可以实现相同的结果,而不必求助于“计算”。

Joe R. 15 年前

CheeseWithCheese说这必须在ACE报告中完成,所以这是我的ACE报告。。。

在ACE中将军事小时smallint转换为AM/PM格式的示例:

select beg_tm, end_tm ...

define
variable utime char(4) 
variable ftime char(7)
end

format

on every row

let utime = beg_tm  {cast beg_tm to char(4). do same for end_tm} 

if utime[1,2] = "00" then let ftime[1,3] = "12:"
if utime[1,2] = "01" then let ftime[1,3] = " 1:"
if utime[1,2] = "02" then let ftime[1,3] = " 2:"
if utime[1,2] = "03" then let ftime[1,3] = " 3:"
if utime[1,2] = "04" then let ftime[1,3] = " 4:"
if utime[1,2] = "05" then let ftime[1,3] = " 5:"
if utime[1,2] = "06" then let ftime[1,3] = " 6:"
if utime[1,2] = "07" then let ftime[1,3] = " 7:"
if utime[1,2] = "08" then let ftime[1,3] = " 8:"
if utime[1,2] = "09" then let ftime[1,3] = " 9:"
if utime[1,2] = "10" then let ftime[1,3] = "10:"
if utime[1,2] = "11" then let ftime[1,3] = "11:"

if utime[1,2] = "12" then let ftime[1,3] = "12:"
if utime[1,2] = "13" then let ftime[1,3] = " 1:"
if utime[1,2] = "14" then let ftime[1,3] = " 2:"
if utime[1,2] = "15" then let ftime[1,3] = " 3:"
if utime[1,2] = "16" then let ftime[1,3] = " 4:"
if utime[1,2] = "17" then let ftime[1,3] = " 5:"
if utime[1,2] = "18" then let ftime[1,3] = " 6:"
if utime[1,2] = "19" then let ftime[1,3] = " 7:"
if utime[1,2] = "20" then let ftime[1,3] = " 8:"
if utime[1,2] = "21" then let ftime[1,3] = " 9:"
if utime[1,2] = "22" then let ftime[1,3] = "10:"
if utime[1,2] = "23" then let ftime[1,3] = "11:"

let ftime[4,5] = utime[3,4]   

if utime[1,2] = "00"
or utime[1,2] = "01"
or utime[1,2] = "02"
or utime[1,2] = "03"
or utime[1,2] = "04"
or utime[1,2] = "05"
or utime[1,2] = "06"
or utime[1,2] = "07"
or utime[1,2] = "08"
or utime[1,2] = "09"
or utime[1,2] = "10"
or utime[1,2] = "11" then let ftime[6,7] = "AM"

if utime[1,2] = "12"
or utime[1,2] = "13"
or utime[1,2] = "14"
or utime[1,2] = "15"
or utime[1,2] = "16"
or utime[1,2] = "17"
or utime[1,2] = "18"
or utime[1,2] = "19"
or utime[1,2] = "20"
or utime[1,2] = "21"
or utime[1,2] = "22"
or utime[1,2] = "23" then let ftime[6,7] = "PM"

print column 1, "UNFORMATTED TIME: ", utime," = FORMATTED TIME: ", ftime

CheeseConQueso 16 年前

长期方法。。。但有效

select  substr((mtg_rec.beg_tm-1200),0,1)||":"||substr((mtg_rec.beg_tm-1200),2,2)||" pm" beg_tm,
            substr((mtg_rec.end_tm-1200),0,1)||":"||substr((mtg_rec.end_tm-1200),2,2)||" pm" end_tm
    from    mtg_rec
    where   mtg_rec.beg_tm between 1300 and 2159
            and mtg_rec.end_tm between 1300 and 2159
    union
    select  substr((mtg_rec.beg_tm-1200),0,1)||":"||substr((mtg_rec.beg_tm-1200),2,2)||" pm" beg_tm,
            substr((mtg_rec.end_tm-1200),0,2)||":"||substr((mtg_rec.end_tm-1200),3,2)||" pm" end_tm
    from    mtg_rec
    where   mtg_rec.beg_tm between 1300 and 2159
            and mtg_rec.end_tm between 2159 and 2400
    union
    select  substr((mtg_rec.beg_tm-1200),0,2)||":"||substr((mtg_rec.beg_tm-1200),3,2)||" pm" beg_tm,
            substr((mtg_rec.end_tm-1200),0,2)||":"||substr((mtg_rec.end_tm-1200),3,2)||" pm" end_tm
            mtg_rec.days
    from    mtg_rec
    where   mtg_rec.beg_tm between 2159 and 2400
            and mtg_rec.end_tm between 2159 and 2400
    union
     select substr((mtg_rec.beg_tm),0,1)||":"||(substr((mtg_rec.beg_tm),2,2))||" am" beg_tm,
            substr((mtg_rec.end_tm),0,1)||":"||(substr((mtg_rec.end_tm),2,2))||" am" end_tm
            mtg_rec.days
    from    mtg_rec
    where   mtg_rec.beg_tm between 0 and 959
            and mtg_rec.end_tm between 0 and 959
    union
     select substr((mtg_rec.beg_tm),0,2)||":"||(substr((mtg_rec.beg_tm),3,2))||" am" beg_tm,
            substr((mtg_rec.end_tm),0,2)||":"||(substr((mtg_rec.end_tm),3,2))||" am" end_tm
            mtg_rec.days
    from    mtg_rec
    where   mtg_rec.beg_tm between 1000 and 1259
            and mtg_rec.end_tm between 1000 and 1259
    union
     select cast(beg_tm as varchar(4)),
            cast(end_tm as varchar(4))
    from    mtg_rec
    where   mtg_rec.beg_tm = 0
            and mtg_rec.end_tm = 0
    into temp time_machine with no log;