Menu
  1. Accueil
  2. Tech Forum
  3. Programming

Converting MYSQL VARCHAR column to TIME

Report
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
 -
 Th4Ac3 -
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.
See more

6 replies

Answer 1 / 6

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
Answer 2 / 6

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.
Answer 3 / 6
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
 118
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.
Answer 4 / 6
Posts
2
Registration date
Monday August 31, 2020
Status
Member
Last seen
September 13, 2020
Follow this link to know more about Sql database fundamentals ...
https://tequality.tech/course/sql
Answer 5 / 6

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.
Answer 6 / 6

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
Reply Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!