如何在MySQL中将奇怪的varchar“时间”转换为实时?

Asked
Viewd9596

2

我有一个时间值作为varchar(4)存储在数据库中,我需要将其转换为实时值。

例如,如果时间是“ 23:59”,我想返回11:59 PM。

问题在于小时和分钟之间没有“:”。所以当我运行这个查询

 SELECT TIME_FORMAT('2359', '%h:%i');    -- 12:23, wtf??
 

但是,如果我执行此操作:

 SELECT TIME_FORMAT('23:59', '%h:%i');   -- returns 11:59 as expected.
 

因此,总结一下: 1.时间以varchar(4)的形式存储在数据库中。示例:

 1200, 1201, 0153, 1364, 1923
 
  1. 我希望时间以12小时制返回,其中带有一个冒号。

我的脑痛,这比我意识到的要容易得多...

像这样,但是对于mysql 在SQL Server中将varchar转换为日期时间 mysql 12小时到24小时的时间转换

  • 在您的第一个实验中,我想MySQL将'2359'解释为23分钟(em)和59秒(em)(而12表示凌晨12点)。以前从未在MySQL中注意到这一点,并且这里没有数据库可以重现,因此请一salt而就。

    Arthur ReutenauerNovember 13, 2009 20:43

5 个答案

2

没关系,这很好:

 TIME_FORMAT(CONCAT(SUBSTRING(THE_TIME, 1,2), ':', SUBSTRING(THE_TIME, 3,4)), '%h%i')
 
  • 明智的,尽管我无法确定是否比我的清楚。

    wallykNovember 13, 2009 21:00
1

使用一个名为test的表,该表具有一个名为string的列并带有值

 2300
2100
1200
0430
0430
 

有查询

 select concat(time_format(concat(substring(string,-4,2),':',substring(string,3)),'%h:%i'),
   case when string >= 1200 then ' PM' else ' AM' end)
from test;
 

你得到

 11:00 PM
09:00 PM
12:00 PM
04:30 AM
04:30 AM
 
0

这是一个快速的技巧,但是由于您缺少4位数字之间的“:”,因此如何插入丢失的冒号。

 SELECT TIME_FORMAT( CONCAT( SUBSTRING('2359', 0, 2), ':', 
                            SUBSTRING('2359', 3, 2)), '%h:%i');
 

当然,用时间列名称替换2359。

1

该查询如何?

 SELECT TIME_FORMAT(STR_TO_DATE('2359', '%H%i'), '%h:%i %p');
 

这是结果:

+ ------------------------------------------------------ +
|TIME_FORMAT(STR_TO_DATE('2359','%H%i'),'%h:%i%p')|
+ ------------------------------------------------------ +
|下午11:59 |
+ ------------------------------------------------------ +
  • 我编辑了查询,并将%h替换为%H。现在对我来说可以正常使用(MySQL 5.1)

    Miroslav BajtošNovember 13, 2009 21:12
  • 因为STR_TO_DATE返回NULL,所以返回NULL。

    wallykNovember 13, 2009 20:59
1

如果包括几秒钟,它看起来将按预期运行:

mysql> SELECT TIME_FORMAT(235900,'%h%i%s');
+ --------------------------------- +
|TIME_FORMAT(235900,'%h%i%s')|
+ --------------------------------- +
|0000 00 00 11 59 00 |
+ --------------------------------- +
设置1行(0.00秒)

我什至删除了引号-它可以使用整数就可以了。

为了确认它在做正确的事情:

mysql> SELECT TIME_FORMAT(235901,'%h%i%s');
+ --------------------------------- +
|TIME_FORMAT(235901,'%h%i%s')|
+ --------------------------------- +
|11 59 01 |
+ --------------------------------- +
设置1行(0.00秒)

我尝试了几种组合,例如235960 236059 235999,它们都返回NULL。

  • 该字段为varchar(4),我永远不会有235900

    eviljackNovember 13, 2009 20:53