CODEWITHSUNDEEP
pythonmysqlsql
Cripto
Cripto

Reputation: 3751

Convert mysql varchar to float or epoch time?

I have a databse that I loaded using python with time.time() which results to a time in seconds that looks like 1370449004.766663 and so on.

These are in a column named time that is of type varchar.

How can I convert this column to be able to utilize the from_unixtime() command discussed here.

When I do this now:

SELECT UNIX_TIMESTAMP(time) from table;

the output is 0. where time is a varchar column with the output of time.time from my python command.

However, my main goal is to convert this column to be noted as a float column so that I can do math on it without having to cast it each time.

Any suggestions?

Upvotes: 0

Views: 424

Answers (1)

Andomar
Andomar

Reputation: 238086

unix_timestamp() takes a date as input. It doesn't make sense to call it on a varchar like 1370449004.766663.

To convert a varchar column to a float column, you could:

alter table YourTable add FloatColumn float;
update YourTable set FloatColumn= convert(time, float);

It's an even better idea to store a date as a datetime column:

alter table YourTable add DateTimeColumn datetime;
update YourTable set DateTimeColumn = from_unixtime(convert(time, float));

Upvotes: 1

Related Questions