Need help with time conversion

[eluser]CI_Newb[/eluser]
Right now i'm converting my old app to codeigniter.

One change I made was to alter the way dates and times are stored in the database.

Right now, they are stored in 2 columns
tdate = DATE column type storing (Y-m-d)
ttime = TIME column type storing (H:i)

Codeigniter version
submit_date = DATE column type storing (Y-m-d)
submit_time = VARCHAR(10) column type storing (U) epoch time

There are currently 18,000 rows so doing by hand is out of the question.

How can change H:i to unix epoch time in the database?

[eluser]flaky[/eluser]
First of all don't save your unix timestamps in varchar(10), querying it will give you a performance penalty of about 100% or more. Use int or biging (depending on how long is your timestamp).

As to updating your column with the unix timestamp
use the following query

Code:

UPDATE table_name SET table_name.timestamp_col = UNIX_TIMESTAMP(CONCAT(table_name.date_col, ' ', table_name.time_col))

[eluser]CI_Newb[/eluser]
Very good to know about the column type, didn't even realize that.

Thanks for the query, worked like a charm