Date format in CSV import

[svensen]

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:

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)

[Julius Stricker]

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

[Wayne]

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)