SQL - 将24小时（“军事”）时间（2145）转换为“AM / PM时间” （下午9:45） [英] SQL - Converting 24-hour ("military") time (2145) to "AM/PM time" (9:45 pm)
问题描述
编辑我正在IBM Informix Dynamic Server V 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）
beg_tm = vbeg_tm - 1200
其中vbeg_tm是声明的char（4）变量
编辑
这个工作时间> = 1300（EXCEPT FOR 2230 !!）
select substr（（beg_tm-1200），0,1）||：|| substr（（beg_tm-1200），2,2）来自mtg_rec其中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））||ambeg_tm来自mtg_rec其中mtg_no = 1;
编辑
Jonathan Leffler表达方法中使用的转换语法的变化
SELECT beg_tm，
cast（（MOD beg_tm / 100 + 11,12）+ 1）作为VARCHAR（2））|| '：'||
SUBSTRING（cast（（MOD（beg_tm，100）+ 100）作为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）作为VARCHAR（2））|| '：'||
SUBSTRING（cast（（MOD（end_tm，100）+ 100）作为CHAR（3））FROM 2）||
SUBSTRING（'am pm'FROM（MOD（cast（（end_tm / 1200）as INT），2）* 3）+ 1 FOR 3）
FROM mtg_rec
其中mtg_no = 39;
请注意， SO 440061 关于在12小时和24小时之间转换时间符号（与此转换相反）;这不是微不足道，因为上午12:45在上午1:15之前半小时。
接下来，请注意，IDS版本7.31终于达到了服务的终止在2009-09-30;它不再是支持的产品。
您的版本号应更精确;例如，7.30.UC1和7.31.UD8之间有很大的差异。
但是，您应该可以使用TO_CHAR（）函数根据需要格式化时间。虽然这个参考是 IDS 11.50信息中心，我相信您将能够在7.31（不一定在7.30中使用它），但是在过去十年的大部分时间你不应该使用它。 >
它有一个'％R'格式说明符24小时的时间。它也引用你' GL_DATETIME '，其中％I给你12小时的时间，'％p'给你am / pm指标。我还发现一个7.31.UD8的IDS实例来验证：
select to_char（datetime（2009-01-01 16： 15:14）年份第二，'％I：％M％p'）
从双重;
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符号那么紧凑。 p>
Steve Kass显示SQL Server符号：
select
（（@ milTime / 100 + 11）％12 + 1作为varchar（2））
+'：'
+ substring ），2,2）
+''
+ substring（'ap'，@ milTime / 1200％2 + 1,1）
+'m'
让小时正确的技巧是整齐的 - 谢谢Steve！
假设表为：
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（ampmFROM（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;
返回（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），您可以手牵手。
说明表达式和存储过程之间的（一个小变体）的等价性： / p>
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/ pre>
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
2359 11:59 pm 11:59 pm
2400 12:00 am 12:00 am
此存储过程现在可以在ACE报告中的单个SELECT语句中多次使用，而不用担心。
[从原来的p oster关于不工作... ]
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;
返回（hh ||'：'|| m3 [2,3] || a3）;
END PROCEDURE;
这是在Solaris 10上对IDS 7.31.UD8进行了测试，并且工作正常。我不明白语法错误报告;但是有一个外部机会存在版本依赖关系 - 它始终至关重要的是报告版本号和平台，以防万一。请注意，我很小心地记录各种事情的工作原理;这不是一个意外，也不是一件好事 - 它是基于多年的经验。
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.FC9beg_tm and end_tm
Sample values
beg_tm 545 end_tm 815 beg_tm 1245 end_tm 1330Sample output
beg_tm 5:45 am end_tm 8:15 am beg_tm 12:45 pm end_tm 1:30 pmI had this working in Perl, but I'm looking for a way to do it with SQL and case statements.
Is this even possible?
EDITEssentially, 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 - 1200Where 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;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;
EDIT
Variation of casting syntax used in Jonathan Leffler's expression approachSELECT 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;
解决方案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.
Next, please note that IDS version 7.31 finally reached its end of service on 2009-09-30; it is no longer a supported product.
You should be more precise with your version number; there are considerable differences between 7.30.UC1 and 7.31.UD8, for instance.
However, you should be able to use the TO_CHAR() function to format times as you need. Although this reference is to the IDS 11.50 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).
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
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 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';The trick for getting the hour correct is neat - thanks Steve!
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;The SUBSTRING notation using FROM and FOR is standard SQL notation - weird, but so.
Example results:
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 amCaution: 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.
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...
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;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.
This stored procedure should work on any version of Informix (OnLine 5.x, SE 7.x, IDS 7.x or 9.x, 10.00, or 11.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;Which produces the result:
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 amThis stored procedure can now be used multiple times in a single SELECT statement inside your ACE report without further ado.
[After comments from the original poster about not working...]
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;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屋！
