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

    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
    20.01.1981
    01/01/1981
    1981/01/10
    01/20/1981
    ... and so on

    You won't get it to work?

    WHY?

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

    Code:
    date +%s -d 1981-01-20
    348793200

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

    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?

    Regards
    Sven

    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

    Default

    Hi,

    "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.

    Code:
    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.

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

Bookmarks

Posting Permissions

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