SQL - Преобразование 24-часового (“военного”) времени (2145) к “/PM время” (21:45)
Поддержки CLR инициализаторы модуля . Необходимо было бы взломать C++ / код CLI или ilasm.exe для использования их.
7 ответов
Обратите внимание, что на 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 Information Center , я считаю, что вы сможете использовать его в версии 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
Я вижу, перечитав вопрос, что на самом деле у вас есть значения SMALLINT в диапазоне 0000..2359, и вам нужно их преобразовать. Часто я d, обратите внимание, что Informix имеет тип для хранения таких значений - DATETIME HOUR TO MINUTE - но я признаю, что он занимает на диске 3 байта вместо 2, поэтому он не такой компактный, как запись SMALLINT. нотация 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 для 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;
Нотация SUBSTRING с использованием FROM и FOR является стандартным 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 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 для 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;
Нотация SUBSTRING с использованием FROM и FOR - это стандартная нотация 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 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 для 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;
Нотация SUBSTRING с использованием FROM и FOR - это стандартная нотация 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 не будет иметь обозначение приведения. значения 559-601 находятся в списке, потому что я столкнулся с проблемой округления вместо усечения из-за отсутствия преобразования в целое число.
Теперь это было протестировано на IDS 11.50; IDS 7.3x не будет иметь обозначения приведения. значения 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.
[ После комментариев исходного постера о том, что он не работает ... ]
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;
Это было протестировано на IDS 7.31.UD8 на Solaris 10 и работало правильно. Я не понимаю сообщенную синтаксическую ошибку; но есть вероятность наличия зависимости версий - всегда критично сообщать номера версий и платформы на всякий случай. Заметьте, что я тщательно документирую, где работали разные вещи; это не случайность и не просто суетливость - это основано на многолетнем опыте.
Вторая попытка mjv все еще не работает. (Например, для 0001 это дает 0: 1.)
Вот решение T-SQL, которое должно работать лучше. Его можно адаптировать к другим диалектам, используя соответствующий синтаксис для конкатенации и SUBSTRING.
Он также работает для военного времени 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';
Here's a non-tested port of Steve Kass's solution to Informix.
Steve's solution itself is well tested under MS SQL Server. I like it better than my previous solutions because the conversion to am/pm time is exclusively done algebraically not requiring the help of any branching (with CASE statements and such).
Substitute the @milTime with column name if the numeric "military time" comes from the database. The @ variable is only there for test.
--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';
For reference here's my [fixed], CASE-based, solution for 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
Не уверен насчет 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')например, 1-> ' 00:01 AM ', 545 -> '05: 45 AM', 1215 -> '12: 15 PM '
Oracle To_Date и To_Char являются проприетарными, но яЯ уверен, что есть стандартные функции SQL или Informix, которые достигают того же результата, не прибегая к «вычислениям».
ДЛИТЕЛЬНЫЙ подход ... но работает
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;
ЧЕРТОВОЙ ЧЕРЕЗЕЗЕЕ, что должно быть сделано в отчете ACE, поэтому вот мой отчет Ace ...
Пример преобразования военного часа Smallint в формат AM / PM в ACE:
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
А, такой же пользователь Джензабара (Джонатан, не будь слишком жестоким по отношению к схемам. Им буквально десятилетия). Удивлен, что вы не спросили об этом в списке CX-Tech. Я бы отправил вам готовую к RCS хранимую процедуру для CX.
-sw
{
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)