asked on

How to convert Unix Epoch to Timestamp(14) with a SQL Statement

I have several large MySQL databases, that, while being moved from one server to another, using MySQL Import and Export, lost the original Timestamp(14) formatting.

On the original server, all dates and times were stored as Timestamp(14): [i.e. YYYYMMDDhhmmss]

On the new server, they are stored as Unix Epoch (I believe): [i.e. YYYY-MM-DD hh:mm:ss]

Rather than switching all of my code to change the timestamp every time I need to access something from the database, I was looking for a SQL Update statement, which I could run on the databases to convert them all back to Timestamp(14).

If anyone can help it would be much appreciated.

Thanks!

Kim Ryan

You could try modifying the colum type. I would do it on a test table and check for warnings on conversion
ALTER TABLE tbl_name MODIFY col_name TIMEDSTAMP(14);

ASKER CERTIFIED SOLUTION

NovaDenizen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

timberlinegt

ASKER

Ah Ha, no it's not the same MySQL version. The old server is running 4.0.xx and the new one is 4.1.xx. I didn't think there was a huge difference, but according to the MySQL knowledgebase they changed the timestamp stuff in 4.1.xx. I also found out that the issue had to do with our script that contacted a time server and compared that to the database. The server being contacted moved and the address was different. We also looked into the +0 in the SELECT statement which also fixed the problem.

Thanks!