SQL - 将 24 小时(“军用")时间 (2145) 转换为“AM/PM 时间"；(晚上 9 点 45 分) [英] SQL - Converting 24-hour ("military") time (2145) to "AM/PM time" (9:45 pm)
问题描述
我正在使用 2 个字段，这些字段存储为 smallint 军事结构化时间.
编辑 我在 IBM Informix Dynamic Server Version 10.00.FC9 上运行
I have 2 fields I'm working with that are stored as smallint military structured times.
Edit I'm running on IBM Informix Dynamic Server Version 10.00.FC9
beg_tm 和 end_tm
beg_tm and 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 语句来完成这项工作.
I had this working in Perl, but I'm looking for a way to do it with SQL and case statements.
这甚至可能吗?<小时>编辑
基本上，必须在 ACE 报告中使用这种格式.我找不到使用简单块在输出部分中格式化它的方法
Essentially, this formatting has to be used in an ACE report. I couldn't find a way to format it within the output section using simple blocks of
if(beg_tm>=1300) then
beg_tm = vbeg_tm - 1200
其中 vbeg_tm 是已声明的 char(4) 变量<小时>编辑这适用于 >=1300 小时(2230 除外！！)
Where vbeg_tm is a declared char(4) variable
EDIT This works for hours >=1300 (EXCEPT FOR 2230 !!)
select substr((beg_tm-1200),0,1)||":"||substr((beg_tm-1200),2,2) from mtg_rec where beg_tm>=1300;
这可以工作几个小时 <1200(有时.... 10:40 失败)
This works for hours < 1200 (sometimes.... 10:40 is failing)
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 的表达式方法中使用的转换语法的变体
EDIT
Variation of casting syntax used in Jonathan Leffler's expression approach
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;
推荐答案
请注意，在 SO 440061 关于在 12 小时和 24 小时符号之间转换时间(与此转换相反)；这不是微不足道的，因为凌晨 12:45 比凌晨 1:15 早了半小时.
Please note that there is useful information at SO 440061 about converting between 12 hour and 24 hour notations for time (the opposite of this conversion); it isn't trivial, because 12:45 am comes half an hour before 1:15 am.
接下来请注意，Informix (IDS — Informix Dynamic Server) 7.31 版终于在 2009 年 9 月 30 日终止服务；它不再是受支持的产品.
Next, please note that Informix (IDS — Informix Dynamic Server) version 7.31 finally reached its end of service on 2009-09-30; it is no longer a supported product.
您应该更准确地使用您的版本号；例如，7.30.UC1 和 7.31.UD8 之间存在相当大的差异.
You should be more precise with your version number; there are considerable differences between 7.30.UC1 and 7.31.UD8, for instance.
但是，您应该能够使用 TO_CHAR() 函数根据需要格式化时间.尽管此参考是针对 IDS 12.10信息中心，我相信您将能够在 7.31 中使用它(不一定在 7.30 中，但在过去十年的大部分时间里您不应该使用它).
However, you should be able to use the TO_CHAR() function to format times as you need. Although this reference is to the IDS 12.10 Information Center, I believe that you will be able to use it in 7.31 (not necessarily in 7.30, but you should not have been using that for most of the last decade).
它说，有一个 '%R' 格式说明符表示 24 小时制.它还向您推荐 'GL_DATETIME'，其中 '%I' 为您提供 12 小时时间，而 '%p' 为您提供上午/下午指示符.我还找到了一个 7.31.UD8 的 IDS 实例来验证这一点:
There is a '%R' format specifier for 24-hour time, it says. It also refers you to 'GL_DATETIME', where it says '%I' gives you the 12-hour time and '%p' gives you the am/pm indicator. I also found a 7.31.UD8 instance of IDS to validate this:
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 表示法紧凑.
I see from re-reading the question that you actually have SMALLINT values in the range 0000..2359 and need to get those converted. Often, I'd point out that Informix has a type for storing such values - DATETIME HOUR TO MINUTE - but I concede it occupies 3 bytes on disk instead of just 2, so it isn't as compact as a SMALLINT notation.
Steve Kass 展示了 SQL Server 表示法:
Steve Kass showed the SQL Server notation:
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！
The trick for getting the hour correct is neat - thanks Steve!
翻译成 IDS 11.50 的 Informix，假设表是:
Translated into Informix for IDS 11.50, assuming that the table is:
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 表示法 - 很奇怪，但确实如此.
The SUBSTRING notation using FROM and FOR is standard SQL notation - weird, but so.
示例结果:
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 在列表中，因为在没有强制转换为整数的情况下，我遇到了舍入而不是截断的问题.
Caution: the values 559-601 are in the list because I ran into a problem with rounding instead of truncation in the absence of the cast to integer.
现在，这已在 IDS 11.50 上进行了测试；IDS 7.3x 没有强制转换符号.但是，这不是问题.下一条评论将处理这个问题......
Now, this was tested on IDS 11.50; IDS 7.3x won't have the cast notation. However, that isn't a problem; the next comment was going to deal with that...
作为一个练习如何在没有条件等的情况下在 SQL 中编写表达式，这很有趣，但如果有人在整个套件中不止一次地写过，我会因为缺乏模块化而开枪.显然，这需要一个存储过程 - 并且存储过程不需要(显式)强制转换或其他一些技巧，尽管分配强制执行隐式强制转换:
As an exercise in how to write the expression in SQL without conditionals, etc, this is interesting, but if anyone wrote that more than once in an entire suite, I'd shoot them for lack of modularization. Clearly, this requires a stored procedure - and a stored procedure doesn't need the (explicit) casts or some of the other trickery, though the assignments enforce implicit casts:
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]' 表示法是子字符串运算符的原始形式；原始的，因为(出于我仍然无法理解的原因)下标必须是文字整数(不是变量，不是表达式).它恰好在这里有用；总的来说，这很令人沮丧.
The Informix '[2,3]' notation is a primitive form of sub-string operator; primitive because (for reasons that still elude me) the subscripts must be literal integers (not variables, not expressions). It happens to work usefully here; in general, it is frustrating.
此存储过程应该适用于您可以使用的任何版本的 Informix(OnLine 5.x、SE 7.x、IDS 7.x 或 9.x、10.00、11.x、12.x).
This stored procedure should work on any version of Informix (OnLine 5.x, SE 7.x, IDS 7.x or 9.x, 10.00, 11.x, 12.x) that you can lay hands on.
为了说明表达式和存储过程(一个小变体)的等价性:
To illustrate the equivalence of (a minor variant on) the expression and the stored procedure:
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 语句中多次使用此存储过程，无需多言.
This stored procedure can now be used multiple times in a single SELECT statement inside your ACE report without further ado.
[在原发帖人评论说不工作...]
IDS 7.31 不处理传递给 MOD() 函数的非整数值.因此，除法必须存储在显式整数变量中 - 因此:
IDS 7.31 doesn't handle non-integer values passed to the MOD() function. Consequently, the divisions have to be stored in an explicit integer variable - thus:
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 上进行了测试，并且工作正常.我不明白报告的语法错误；但是存在版本依赖性的外部可能性 - 报告版本号和平台以防万一，始终至关重要.请注意，我很小心地记录了各种工作的位置；这不是偶然，也不是大惊小怪——这是基于多年的经验.
This was tested on IDS 7.31.UD8 on Solaris 10 and worked correctly. I don't understand the syntax error reported; but there is an outside chance of there being a version dependency - it is always crucial to report version numbers and platforms just in case. Notice that I'm careful to document where various things worked; that isn't an accident, nor is it just fussiness -- it is based on many years of experience.
这篇关于SQL - 将 24 小时(“军用")时间 (2145) 转换为“AM/PM 时间"；(晚上 9 点 45 分)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
