Menu
  1. Accueil
  2. Tech Forum
  3. Programming

Converting MYSQL VARCHAR column to TIME

Report
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
June 16, 2020
 -
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
June 16, 2020
 -
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

1 reply

Answer 1 / 1
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
June 16, 2020
 248
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.
Reply Ask a question