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

Posted on 2006-06-07
Last Modified: 2010-05-18
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.

Question by:timberlinegt
    LVL 19

    Expert Comment

    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);
    LVL 22

    Accepted Solution

    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?


    Author Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now