3

I have a time value being stored in a database as a varchar(4) and I need to convert it to real time.

for example, if the time is "23:59" I want 11:59PM returned.

The problem is that there is no ":" between the hours and minutes. So when I run this query

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

However if I ran this:

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

So, to sum up: 1. the time is stored as a varchar(4) in the database. Example: 

1200, 1201, 0153, 1364, 1923
  1. I want time returned as 12 hr time with a colon in it.

my brain hurts and this is prb much easier than I realize...

like this, but for mysql Convert varchar into datetime in SQL Server mysql 12 hr to 24 hr time conversion

Community
  • 1
  • 1
eviljack
  • 3,696
  • 8
  • 39
  • 52
  • Re your first experiment, I guess MySQL interprets '2359' as meaning 23 *minutes* and 59 *seconds* (and 12 would be 12 AM, the beginning of the day). Never noticed that before in MySQL, and don't have a database handy here to reproduce, so take that with a grain of salt. – Arthur Reutenauer Nov 13 '09 at 20:43
  • Your first query is a truncated time value. MySQL interprets it as a 00:23:59, but the 59 part is not displayed by the format '%h:%i' https://dev.mysql.com/doc/refman/5.5/en/time.html "MySQL interprets abbreviated values without colons using the assumption that the two rightmost digits represent seconds (that is, as elapsed time rather than as time of day). For example, you might think of '1112' and 1112 as meaning '11:12:00' (12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). " – ExcessOperatorHeadspace May 20 '19 at 21:14

5 Answers5

2

never mind, this works fine:

TIME_FORMAT(CONCAT(SUBSTRING(THE_TIME, 1,2), ':', SUBSTRING(THE_TIME, 3,4)), '%h%i')
Faisal
  • 4,591
  • 3
  • 40
  • 49
eviljack
  • 3,696
  • 8
  • 39
  • 52
1

What about this query?

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

Here is the result:

+------------------------------------------------------+
| TIME_FORMAT(STR_TO_DATE('2359', '%H%i'), '%h:%i %p') |
+------------------------------------------------------+
| 11:59 PM                                             | 
+------------------------------------------------------+
Miroslav Bajtoš
  • 10,667
  • 1
  • 41
  • 99
1

It looks like it performs as expected if seconds are included:

mysql> SELECT TIME_FORMAT(235900, '%h %i %s');
+---------------------------------+
| TIME_FORMAT(235900, '%h %i %s') |
+---------------------------------+
| 0000 00 00 11 59 00             |
+---------------------------------+
1 row in set (0.00 sec)

I even dropped the quotes—it works with an integer just fine.

For confirmation it is doing the right thing:

mysql> SELECT TIME_FORMAT(235901, '%h %i %s');
+---------------------------------+
| TIME_FORMAT(235901, '%h %i %s') |
+---------------------------------+
| 11 59 01                        |
+---------------------------------+
1 row in set (0.00 sec)

I tried several combinations like 235960 236059 235999 and they all return NULL.

wallyk
  • 56,922
  • 16
  • 83
  • 148
1

Using a table named test with a column named string and with values

2300
2100
1200
0430
0430

With query 

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;

You get,

11:00 PM
09:00 PM
12:00 PM
04:30 AM
04:30 AM
Elliot Vargas
  • 20,499
  • 11
  • 34
  • 36
0

This is a quick hack, but since you're missing the ":" between the 4 digits number how about inserting the missing colon.

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

Of course, replace the 2359 with the time column name.

Yada
  • 30,349
  • 24
  • 103
  • 144