Link to home
Start Free TrialLog in
Avatar of j_hoadley
j_hoadley

asked on

Need help converting dates before 1970 from mSQL to MySQL

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.

Example:

  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:
http://www.hughes.com.au/library/msql/msql30-manual.pdf

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


ASKER CERTIFIED SOLUTION
Avatar of m1tk4
m1tk4
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of j_hoadley
j_hoadley

ASKER

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.