Homepage | Products | OX Knowledge Base | Support | Try Now | Contact | Company
OX Logo
Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Hamburg, Germany

    Exclamation Date format in CSV import

    Attention all OX Users!

    If you ever tried to import your contacts from CSV into Open-Xchange (or 1&1 MailExchange) you almost certainly would have failed in doing so (as I did) if you have any birthdates or anniversaries in them:

    Fehlermeldung: Angegebene Zeichenkette 1981-01-20 konnte nicht in ein Datum konvertiert werden. (CON-0600,-402838903-6480)

    Well, there actually are a lot of date formats out there, so you could try
    ... and so on

    You won't get it to work?


    You have to THINK like a developer. In UNIX, everything is relative to 1970-01-01. So you give OX a UNIX date:

    date +%s -d 1981-01-20

    Thats better, no error. But the date isn't right, and the year is still 1970.
    So it has to be milliseconds, then:

    Well that's better: January 19, 1981. Almost there. Just add another 12 hours:
    348836400000 (OX for: 20. Jan. 1981)

    Dates before 1970 are negative:
    -481899600000 (24. Sept. 1954 in OX notation)

    Question to OX developers: Is this a bug or a feature?


    OX Build: 6.6.0-6606, 2008-09-03 15:07:54
    Debian etch (64 bit)
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_14-b03)
    Last edited by svensen; 09-16-2008 at 08:11 PM. Reason: added OX and JAVA version infos

  2. #2



    "1981-01-20" is a valid vCard format for BDAY and the import should work with the upcoming version:

    OX Build: 6.6.0-6609

  3. #3
    Wayne Guest

    Default Migrating from Group Office to Open-Xchange

    I had a bit of a problem in this area. It was decided to dump existing information to CSV as an intermediary step. The dates are currently stored in YYYY-MM-DD, MySQL DATE format.

    A unique condition is that a null value was actually stored as 0000-00-00. I therefore had to return an empty string for this condition, and a formatted date for a valid date.

    The standard UNIX_TIMESTAMP provides the right format, but for my time values I had to multiply the result by 1000 to account for milliseconds, as mentioned in svensen's post.

    The following is the error I was recieving.

    Error: Could not convert given string 0000-00-00 to a date. (CON-0600, -1606298603-3304)
    The following is the field as it appears in my SELECT statement.

    IF(birthday='0000-00-00','',UNIX_TIMESTAMP(birthday) * 1000)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts