Converting MYSQL VARCHAR column to TIME Closed

Question

Hi All,

I have a database with 2 columns which hold time values (usually in the hh:mm:ss format - see later in the post). Unfortunately during the planning stage the columns were set as VARCHAR entries. Obviously it is hard to do maths with these values. Is there an easy way to change the values into TIME so I can find a duration between these 2 columns by subtracting a value in one column from the value of the second column. These values are on the same row.

A further complication, during the development of the database, some columns were not in place at the start of the project so have no entries in them. Would this be a problem? Im happy to insert any time for this (eg 00:00:00 or 23:59:59). Also, another issue might be the format of some other entries. Some are logged as hh:mm, and others as hh:mm:ss . Do I need to add anything to these values as well, or is there a solution that will add 0 seconds to these values?

I bet its not as simple as just changing the column properties is it?

Thank you all for your assistance.

Vladtheemailer · Answer

Good afternoon brian, its nice to see you back.

Ise the command
Alter table TABLENAME
Alter column columnname newdatatype(size)

As for the bulls, it will only matter if you have set the NOT NuLl constraint on the table.

I hope this helps out, but then again they wont let me post here, so you probably will receive no help as no one here knows anything really.

Have fun.

Mark

Vladtheemailer · Answer

Good afternoon brian, its nice to see you back.

Use the command
Alter table TABLENAME
Alter column columnname newdatatype(size)

As for the nulls, it will only matter if you have set the NOT Null constraint on the table.

I hope this helps out.

Have fun.

BrianGreen · Answer

Thanks for the try, but doing this I get the message:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Timestamp(6)' at line 2

I have also tried to use Timestamp(8) but get the same message (the current time in the VARCHAR  is 8 characters long).

I think I will be forced to convert the VARCHAR into a TIME format in the PHP page and do the maths with the conversion. It will slow the site, but at least it will work ... sort of.

ThAc3 · Answer

Brian, its me, Mark, I hope you get this as this thing doesnt allow me to correctly answer posts.  Use Cast to change values in a query without changing the data type.  So the syntax would be

CAST(Your_column AS DATE() )


I HOPE THIS FINDS YOU WELL.

Th4Ac3 · Answer

Brian, Its me, Mark.  I hope this finds you well.  I have a hard time posting ACTUAL HELPING, WORKING SOLUTIONS here, so don't be surprised when I no longer can answer your returning questions, and there is no one to assist you any longer on your business solutions!

Use the CAST() function to change a value, or match a value, when the variable is not the correct data type.  The syntax would be as follows:

SELECT ....
From ........
Where........
AND Period.END_DATE >= Cast("SOMEVARIABLE" AS DATE)


I hope this helps.  Sorry if it take a long time to get back, if there are any follow ups.

//ark

Converting MYSQL VARCHAR column to TIME

5 responses

Similar discussions

Subscribe To Our Newsletter!