Convert Time help

[topflight]

In my table I have a column called "dtime" and "atime". However when I received the CSV and uploaded it to the database the times were not in time format they just like a simple string for instance, 1720 is in one of the rows but instead of saying 17:20 it says 1720. I have over 13,000 records. May somebody please give me an example on how I can change all those to the correct time format thanks.

[PFMaBiSmAd]

One way (gives a TIME value with :00 seconds) -

SELECT STR_TO_DATE('1720', '%H%i');

 

Another way (gives a string in the format you are showing) -

SELECT CONCAT(LEFT('1720', 2),':',RIGHT('1720',2));

 

Replace the '1720' term in each of the above with your column name (with no single-quotes.)

 

Are the hours always two digits, with leading zeros?

 

[topflight]

But I have 1300 rows with different times. Also what about the times that are like: 620 how can I make them say 6:20?

[Ken2k7]

Why do you keep starting new topics of the same issues?

 

http://www.phpfreaks.com/forums/index.php/topic,295750.msg1400625.html#msg1400625

[PFMaBiSmAd]

The following will produce a TIME value from either a xxx starting value or a xxxx starting value -

SELECT TIME(CONCAT('630','00'));

 

As previously stated, you would replace the 'xxx' term with wherever your starting value is at.

[topflight]

So how should I do this if I have over 13000 records?

[PFMaBiSmAd]

Kind of depends of where the data is now and where you want to get it to. Do you want to do this when you import CSV data or is it already in a column in a table and you want to set an actual column to the converted valued?

[topflight]

Kind of depends of where the data is now and where you want to get it to. Do you want to do this when you import CSV data or is it already in a column in a table and you want to set an actual column to the converted valued?

 

it already in a column in a table and I want to update an actual column to the converted valued?

[PFMaBiSmAd]

Same concept as used in your other thread dealing with converting dates -

 

UPDATE your_table SET time_column = TIME(CONCAT(other_column,'00'));