how to convert weird varchar "time" to real time in mysql?

Question

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

2. 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

Solution 1

never mind, this works fine:

TIME_FORMAT(CONCAT(SUBSTRING(THE_TIME, 1,2), ':', SUBSTRING(THE_TIME, 3,4)), '%h%i')

Solution 2

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                                             | 
+------------------------------------------------------+

Solution 3

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.

Solution 4

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

View more solutions