Need help converting dates before 1970 from mSQL to MySQL

Posted on 2006-04-28
Last Modified: 2012-08-13
I need help migrating an application from mSQL to MySQL.

My mSQL application stores birth dates. In mSQL these are stored as unixtime negative numbers. That's fine for mSQL (where the included Lite scripting language has functions to convert to day, month, year, etc.), but once the data is imported  into MySQL there are no functions (that I know of) to manipulate these pre-1970 dates. Using FROM_UNIXTIME() just returns NULL for these pre-1970 dates.


  mysql> SELECT birthdate, last_visit FROM record WHERE first_name = 'TIM' AND last_name = 'HEEZLOCK';
  | birthdate    | last_visit  |
  | -714909600 | 812383200 |
  1 row in set (0.26 sec)

  mysql> SELECT FROM_UNIXTIME(birthdate), FROM_UNIXTIME(last_visit) FROM record WHERE
  first_name = 'TIM' AND last_name = 'HEEZLOCK';
  | FROM_UNIXTIME(birthdate) | FROM_UNIXTIME(last_visit) |
  | NULL                   | 1995-09-29 07:00:00     |
  1 row in set (0.26 sec)

Two choices I see are to export the data from mSQL as YYYY-MM-DD HH:MM:SS and store as DATETIME or VARCHAR, or to keep the dates as negative number in MySQL and somehow combine existing date/time functions to convert them on the fly to YYYY-MM-DD HH:MM:SS.  It would seem that coverting them inside mSQL is the way to go, but I wouldn't know how to utilize the C libraries or the Lite programming language that comes with mSQL. Note that I do not have to do complex calculations on these dates, once in MySQL.

Anyone have any ideas?  Any mSQL experts out there?  :)

Here is the mSQL 3 users guide:

I hope I have explained the problem well. Thanks in advance for any help you can provide.

Question by:j_hoadley
    LVL 15

    Accepted Solution

    It's actually quite easy - first of all, you have to remember that Unix timestamps are just number of seconds since Jan 1 1970, so if the dates are before 1/1/70 the number will be negative. In other words, instead of using from_unixtime(birthdate) you could just do

    date_add('19700101', interval birthdate second);

    Author Comment

    Ding-ding-ding-ding! You're right. That's it. Thank you. You get all the points.

    I thought I had tried that, so I went back in my client history and found that I had, but I had used the wrong syntax for SECOND. I had tried SECONDS and SEC, but not SECOND. That was sloppy and I gave up too soon.

    Thanks again, you have helped me immensely.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    758 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

    11 Experts available now in Live!

    Get 1:1 Help Now