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

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.


  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.

1 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);
j_hoadleyAuthor Commented:
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.


Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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