List:General Discussion« Previous MessageNext Message »
From:Jani Tolonen Date:September 1 1999 5:53am
Subject:Inputting email date fields into Mysql date fields
View as plain text  
Wayne Schuller writes:
 > hi everyone,
 > 
 > I'm writing a mysql based email client. I have a date field for the email 
 > messages which is in DATETIME format. I'm having a hard time trying to 
 > figure out how to parse email date headers into a form by which they can be 
 > inserted into a DATETIME field.
 > 
 > An email date header looks like this:
 > Date: Tue, 31 Aug 1999 17:26:57 +1000
 > 
 > Getting rid of the "Date:" text is easy. Is there a simple function already 
 > set up to parse the rest of the string and put the result in a form that 
 > mysql will accept for DATETIME?
 > Or does anyone have any code to do this? It seems like a common enough 
 > function, I wanted to ask before doing it manually.
 > 
 > I've combed the mysql manual thoroughly and the C <time.h> functions, but I 
 > can't seem to find what I need.
 > 
 > Any help?
 > 

Hello Wayne,

If you have the newest version of MySQL (something like >= 3.22.23 /
3.23) you have a file in mysql_distribution/tests/mail_to_db.pl.
mail_to_db.pl is a perl script which handles mails from INBOX:ses
to a database, also parsing the date. See 'sub date_parser{}' in
mail_to_db.pl.

The problem with dates in emails is that they vary. Sometimes
you have the weekday name there, sometimes not etc.

Of course you could use mysql function substring, but then you
need to come up with some rule that will always be right,
which may be difficult.

Please have a look at MySQL's string and date functions, try
by combining them to get a solution, or see mail_to_db.pl.
(Also available from the Contrib -page)

Regards,

- Jani

-- 
+---------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___				|
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|		Jani Tolonen	|
|    /*/ /*/ /*/   \*\_   |*|   |*||*|		jani@stripped	|
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|		Helsinki	|
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|_____	Finland		|
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^			|
|             /*/             \*\		Developers Team	|
+---------------------------------------------------------------+
Thread
Inputting email date fields into Mysql date fieldsWayne Schuller1 Sep
  • Inputting email date fields into Mysql date fieldsJani Tolonen1 Sep
Re: Inputting email date fields into Mysql date fieldsFaisal Nasim1 Sep