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.

Who is Participating?
NovaDenizenConnect With a Mentor Commented:
You've got the names of the formats wrong.  YYYYMMDDhhmmss is the timestamp in a numeric context, and YYYY-MM-DD hh:mm:ss is the timestamp in a string context.

Unix ephochal time is the count of seconds since 1970-01-01 00:00:00.  UNIX_TIMESTAMP(NOW()) is 1149789803, roughly 1.15 billion seconds since the UNIX epoch.

SELECT now(), now()+0, timestamp(now()+0);
now() expresses the current timestamp in the default string format.  now()+0 coerces the timestamp into the numeric format.  timestamp(now()+0) coerces the numeric timestamp back into the string format.

Is the field declared to be a TIMESTAMP(14) in the new table, or just a timestamp?  What about your old table?  Are you using the same mysqld version on both?

Kim RyanIT ConsultantCommented:
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);
timberlinegtAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.