CODEWITHSUNDEEP
mysqldatetime
eviljack
eviljack

Reputation: 3716

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

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

Upvotes: 3

Views: 11503

Answers (5)

eviljack
eviljack

Reputation: 3716

never mind, this works fine:

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

Upvotes: 2

Miroslav Bajtoš
Miroslav Bajtoš

Reputation: 10795

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

Upvotes: 1

Elliot Vargas
Elliot Vargas

Reputation: 20819

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

Upvotes: 1

Yada
Yada

Reputation: 31265

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.

Upvotes: 0

wallyk
wallyk

Reputation: 57804

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.

Upvotes: 1

Related Questions