Update Received Date in Folder-Listing

[Nextasy]

I found the mistake in the format of the datum.

here is a part of my sql list file, which the script created:

update mail_item set date=unix_timestamp('2005-07-27 10:36:56') where id=3025;
update mail_item set date=unix_timestamp('Mon, 4 07 2005 09:59:05 +0200 (CEST)') where id=3026;

The first entry will be correct importet into the database, the second not. So, my sed skill isnt good and i hope, that someone could help my to figure out, how i can change the format from "Mon, 4 07 2005 09:59:05 +0200" to "2005-07-04 Mon, 4 07 2005 09:59:05 +0200" via sed

thanx in advance

[area]

I've attached an updated version of the shell script which handles the change in recent versions where there are multiple users per mysql database.

And here are a few extra lines to add to the end of the sed script (/root/scripts/convert_date.sed):
# Mon, 4 07 2005 09:59:05 +0200 (CEST)
s/^[A-Z][a-z][a-z], \([0-9][0-9]*\) \([0-9][0-9]\) \([0-9][0-9][0-9][0-9]\) \([0-9][0-9]:[0-9][0-9]:[0-9][0-9]\) +\([0-9]*.*\)$/\3-\2-\1 \4/
s/-\([1-9]\) /-0\1 /

Rgds - Angus

[Nextasy]

Originally Posted by area

And here are a few extra lines to add to the end of the sed script (/root/scripts/convert_date.sed):
# Mon, 4 07 2005 09:59:05 +0200 (CEST)
s/^[A-Z][a-z][a-z], \([0-9][0-9]*\) \([0-9][0-9]\) \([0-9][0-9][0-9][0-9]\) \([0-9][0-9]:[0-9][0-9]:[0-9][0-9]\) +\([0-9]*.*\)$/\3-\2-\1 \4/
s/-\([1-9]\) /-0\1 /

Rgds - Angus

Thx...you are my personal hero of the day ;-)

Hint: for a couple of mails, i have to set the date of the linux date (01-01-1970)

[Nextasy]

@area

before i made change at the sed script, there was 4114 msg in the database, which has noch date.

Now, there are only 2657 msg left, which where not importet into the database. i think, it depends on different formats of the date.

here is a part of the sql-list file your script created.

update mail_item set date=unix_timestamp('4 10 2006 00:30:29 -0700') where id=272;
update mail_item set date=unix_timestamp('2 10 2006 05:42:49 -0700') where id=273;
update mail_item set date=unix_timestamp('2 10 2006 01:38:48 -0700') where id=274;
update mail_item set date=unix_timestamp('22 03 2006 06:28:02 -0800') where id=275;
update mail_item set date=unix_timestamp('22 03 2006 04:31:01 -0800') where id=276;
update mail_item set date=unix_timestamp('21 03 2006 06:24:51 -0800') where id=277;
update mail_item set date=unix_timestamp('16 11 2005 05:44:16 -0800') where id=278;
update mail_item set date=unix_timestamp('23 09 2005 13:26:59 -0700') where id=279;
update mail_item set date=unix_timestamp('Wed, 29 06 2005 10:12:27 GMT') where id=280;
update mail_item set date=unix_timestamp('Tue, 28 06 2005 10:05:29 GMT') where id=281;
update mail_item set date=unix_timestamp('Thu, 16 06 2005 19:43:36 GMT') where id=282;
update mail_item set date=unix_timestamp('15 06 2005 05:38:39 -0700') where id=283;
update mail_item set date=unix_timestamp('Wed, 15 06 2005 12:09:04 GMT') where id=284;
update mail_item set date=unix_timestamp('14 06 2005 13:24:05 -0700') where id=285;
update mail_item set date=unix_timestamp('Fri, 1 04 2005 09:40:51 GMT') where id=286;
update mail_item set date=unix_timestamp('31 03 2005 03:09:37 -0800') where id=287;
update mail_item set date=unix_timestamp('Sat, 26 03 2005 17:01:05 GMT') where id=288;
update mail_item set date=unix_timestamp('25 03 2005 05:46:15 -0800') where id=289;
update mail_item set date=unix_timestamp('2005-02-21 08:04:42') where id=290;
update mail_item set date=unix_timestamp('2005-02-16 12:02:54') where id=291;
update mail_item set date=unix_timestamp('2005-02-14 16:03:55') where id=292;
update mail_item set date=unix_timestamp('Tue, 8 02 2005 12:00:18 GMT') where id=293;
update mail_item set date=unix_timestamp('7 02 2005 12:01:27 -0800') where id=294;
update mail_item set date=unix_timestamp('Wed, 2 02 2005 20:28:20 GMT') where id=295;
update mail_item set date=unix_timestamp('1 02 2005 22:32:51 -0800') where id=296;
update mail_item set date=unix_timestamp('Tue, 1 02 2005 16:09:45 GMT') where id=297;
update mail_item set date=unix_timestamp('31 01 2005 14:08:36 -0800') where id=298;
update mail_item set date=unix_timestamp('Fri, 17 12 2004 05:26:53 -0800') where id=300;
update mail_item set date=unix_timestamp('Fri, 26 11 2004 19:26:40 -0800') where id=301;
update mail_item set date=unix_timestamp('10 11 2004 07:41:24 -0800') where id=302;
update mail_item set date=unix_timestamp('12 10 2004 17:41:04 -0700') where id=304;
update mail_item set date=unix_timestamp('10 10 2004 17:41:21 -0700') where id=305;
update mail_item set date=unix_timestamp('8 10 2004 18:01:42 -0700') where id=306;
update mail_item set date=unix_timestamp('2004-10-07 08:02:29') where id=307;
update mail_item set date=unix_timestamp('6 10 2004 18:21:08 -0700') where id=308;
update mail_item set date=unix_timestamp('4 10 2004 18:21:22 -0700') where id=309;
update mail_item set date=unix_timestamp('2004-10-04 08:17:06') where id=310;
update mail_item set date=unix_timestamp('2004-10-04 08:16:30') where id=311;
update mail_item set date=unix_timestamp('3 10 2004 18:01:32 -0700') where id=312;
update mail_item set date=unix_timestamp('1 10 2004 18:21:19 -0700') where id=313;
update mail_item set date=unix_timestamp('29 09 2004 18:22:09 -0700') where id=314;
update mail_item set date=unix_timestamp('29 09 2004 02:53:04 -0700') where id=315;
update mail_item set date=unix_timestamp('2004-09-24 19:45:10') where id=319;
update mail_item set date=unix_timestamp('23 09 2004 05:12:00 -0700') where id=320;
update mail_item set date=unix_timestamp('22 09 2004 00:48:59 -0700') where id=321;
update mail_item set date=unix_timestamp('Mon, 9 08 2004 14:51:22 GMT') where id=322;
update mail_item set date=unix_timestamp('Mon, 9 08 2004 10:48:17 GMT') where id=323;
update mail_item set date=unix_timestamp('2004-08-02 21:46:39') where id=324;
update mail_item set date=unix_timestamp('2 08 2004 04:54:08 -0700') where id=325;

can you please make another fix to the sed script?

thanx in advance

[area]

OK - here are some updated sed commands to handle the other date formats found in your raw email files (which, as far as I understand email standards, are non-standard).

Please note that these commands assumes that the dates in your raw emails are in d[d] mm yyyy format.

# Mon, 4 07 2005 09:59:05 +0200 (CEST)
# Wed, 29 06 2005 10:12:27 GMT
# Fri, 1 04 2005 09:40:51 GMT
# 4 10 2006 00:30:29 -0700
# 22 03 2006 06:28:02 -0800
s/^[A-Z][a-z][a-z], //
s/\([0-9][0-9]*\) \([0-9][0-9]\) \([0-9][0-9][0-9][0-9]\) \([0-9][0-9]:[0-9][0-9]:[0-9][0-9]\).*$/\3-\2-\1 \4/
s/-\([1-9]\) /-0\1 /

Please use the sed script, and the shell script posted earlier, at your own risk.

[bobby]

FYI there is a perl module (Date::Parse maybe?) for parsing dates out of email that probably does a better job than us

Also, once you have the date, try using the "zmmailbox addMessage" command with the "--date" option instead of trying to add the X-Zimbra-Received header.

Users of imapsize might consider petitioning the developers of that utility to honor the "internaldate" imap flag. FWIW thunderbird 2 now does this.