Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: Update Received Date in Folder-Listing

  1. #21
    Nextasy is offline Active Member
    Join Date
    Jan 2007
    Location
    Germany
    Posts
    42
    Rep Power
    8

    Default

    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

  2. #22
    area is offline Active Member
    Join Date
    Feb 2006
    Posts
    47
    Rep Power
    9

    Default

    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
    Attached Files Attached Files

  3. #23
    Nextasy is offline Active Member
    Join Date
    Jan 2007
    Location
    Germany
    Posts
    42
    Rep Power
    8

    Default

    Quote Originally Posted by area View Post
    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)

  4. #24
    Nextasy is offline Active Member
    Join Date
    Jan 2007
    Location
    Germany
    Posts
    42
    Rep Power
    8

    Default

    @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

  5. #25
    area is offline Active Member
    Join Date
    Feb 2006
    Posts
    47
    Rep Power
    9

    Default

    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.

  6. #26
    bobby is offline Zimbra Employee
    Join Date
    Nov 2005
    Posts
    518
    Rep Power
    10

    Default

    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.

Page 3 of 3 FirstFirst 123

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Date AND Time in received column
    By skydve1 in forum Users
    Replies: 4
    Last Post: 09-21-2009, 12:28 PM
  2. Zimbra fails after working for 2 weeks
    By Linsys in forum Administrators
    Replies: 10
    Last Post: 10-07-2008, 12:42 AM
  3. Replies: 6
    Last Post: 04-22-2008, 03:33 AM
  4. Replies: 29
    Last Post: 02-18-2007, 07:45 PM
  5. Date Received Is Incorrect in Web Interface
    By wowzimbra in forum Administrators
    Replies: 6
    Last Post: 05-18-2006, 03:08 AM

Posting Permissions

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