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

Question

All,

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 http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server http://stackoverflow.com/questions/986058/mysql-12-hr-to-24-hr-time-conversion

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

Answer 2

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.

Answer 3

nevermind, this works:

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

Answer 4

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.

Answer 5

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