Announcement

Collapse
No announcement yet.

Date format in CSV import

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 08:11 PM. Reason: added OX and JAVA version infos

  • #2
    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

    Comment


    • #3
      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)

      Comment

      Working...
      X