• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 760
  • Last Modified:

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.

1 Solution
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);
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?

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.


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now