Converting MYSQL VARCHAR column to TIME

Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Jun 16, 2020 at 10:14 AM
 Th4Ac3 - Sep 10, 2020 at 09:33 AM
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.
Related:

6 responses

Vladtheemailer
Jun 16, 2020 at 10:27 AM
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
0
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.
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Jun 16, 2020 at 11:25 AM
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.
0
purusottamnath09 Posts 2 Registration date Monday August 31, 2020 Status Member Last seen September 13, 2020
Sep 9, 2020 at 01:22 PM
Follow this link to know more about Sql database fundamentals ...
https://tequality.tech/course/sql
0

Didn't find the answer you are looking for?

Ask a question
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.
0
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
0