Incorrect sent timestamp while importing

[Mark Serellis]

Hello,

I think there may be a bug with pilerimport and pilertest (not sure if it affects normal archiving from SMTP). I have recently completed a huge import into piler from eml files and I am in the process of verifying that everything was imported correctly. For that I wrote my own script to extract basic information from the eml files so that I could run a few queries in MySql. I have noticed that a very large proportion of emails have timestamps that are out by 1 hour. For example:

Example 1

Date: Mon, 1 May 2000 13:43:50 +0100

pilertest and pilerimport gave sent as 957181430, however, my script gives 957185030 which I believe is correct and piler is subtracting an extra hour.

‌

Example 2

Date: Sat, 24 Oct 2020 05:34:06 +0530

pilertest and pilerimport gave sent as 1603494246, however, my script gives 1603497846

‌

Are you able to confirm this for me and let me know if I have some setting incorrect or something that is causing this behaviour? I am a PHP and not a C programmer so cannot reliably read most of the source.

‌

This is a fresh install (from a couple of months ago) on dedicsted Ubuntu 20.04.1 LTS.

Piler version:

• piler 1.3.9, build 998, Janos SUTO [e-mail]
• Build Date: Mon Nov 2 17:14:15 GMT 2020
• ldd version: ldd (Ubuntu GLIBC 2.31-0ubuntu9.1) 2.31
• gcc version: gcc version 9.3.0 (Ubuntu 9.3.0-17ubuntu1~20.04)
• OS: Linux mail-piler 5.4.0-52-generic #57-Ubuntu SMP Thu Oct 15 10:57:00 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
• Configure command: ./configure --localstatedir=/var --with-database=mysql --enable-memcached
• MySQL client library version: 10.4.16
• Extractors: /usr/bin/pdftotext /usr/bin/catdoc /usr/bin/catppt /usr/bin/xls2csv /usr/local/bin/ppthtml /usr/bin/unrtf /usr/bin/tnef libzip

‌

Thank you

Mark

[Mark Serellis]

The piler website says “So please DO NOT create unassigned issues”, however, I cannot see any way to assign a ticket. Please don’t mark as invalid, please assign it if you can.

[Mark Serellis]

Interestingly, if the offset is negative then pilerimport and pilertest give the same timestamp I get. For example:

Mon, 26 Oct 2020 11:33:39 -0600

We both get 1603733619

[Mark Serellis]

A further addition to this: My server’s timezone was set to Europe/London, if I change the timezone to UTC then I get the correct sent time from pilertest. I am surprised your script is affected by the server’s timezone as the date format in an email supplies the timezone but even more strange is that only positive offsets are affected. PHP is not affected by this when parsing the date string.

Is it safe to leave the server as UTC when using LDAP authentication (Active Directory on a Windows Server) or when we get to British Summer Time again will we have problems authenticating because of the offset?

To correct this in the database is it simply a case of updating the metadata table or is there anywhere else I need to update?

Thank you.

[Janos SUTO]

Well, authentication is not affected by the timezone, and you may leave it in the UTC. Regarding the issue you raised, I’m not sure how serious a 1 hour delta is in practice.

[Mark Serellis]

OK, thanks, will leave it on UTC. May I recommend you add that to the installation instructions? Perhaps 1 hour might not be a huge inconvenience in practice, I figured it might be 13 hours for Pacific/Auckland timezone?

[Janos SUTO]

I’ve added the recommendation about UTC zone to the FAQ. Let me know if there’s anything I could add to this issue or we can make it closed.

[Mark Serellis]

Up to you if you want to close. If it were me I would try to locate the bug because it shouldn’t matter the timezone of the server. If I had the C skills then I would have helped track it down Thank you very much for making this excellent application available to us.

[Janos SUTO]

I think the FAQ entry is good enough for now.