Hi,
these changes are in CVS. Changed a bit though. Since there is no
inputting of timestamps other than CURRENT_TIMESTAMP, I have not used
the TO_DATE string yet, but a function to use it is already in db.c :)
db_insert_physmessage() uses CURRENT_TIMESTAMP.
I've tested with PostgreSQL, and it seems to work. If anybody wants to
test with MySQL, please go ahead!
Ilja
Aaron Stone wrote:
Oh, oh I got it! In mysql/dbmysql.c and pgsql/pgsql.c we define variable(s):
MySQL:
const char *TO_DATE = "DATE_FORMAT(%s, \"%%Y-%%m-%%d %%H:%%m:%%s\")";
const char *TO_DATE = "DATE_FORMAT(%s, \"%%Y-%%m-%%d %%H:%%m:%%s\")";
PostgreSQL:
const char *TO_DATE = "TO_DATE(%s, \"YYYY-MM-DD HH:MI:SS\" )";
const char *TO_CHAR = "TO_CHAR(%s, \"YYYY-MM-DD HH:MI:SS\" )";
Then, in db.c, we call it as an extern and use it in queries.
Just one problem: I don't remember if *printf() will do substitution within
the substitutions. We'd rely on having one %s argument in the TO_DATE and
TO_CHAR variables so that we can insert the current date or the column name.
If printf() won't do that, then we'd have to use header files to define a
static string "macro" and stringify it into the queries.
Aaron
Ilja Booij <[EMAIL PROTECTED]> said:
Hi,
I was thinking of the best way to solve this problem.
Clearly, in the end, we should not be dependent on the format in which
the database stores the DATE-format. This calls for a solution like the
one that Aaron pointed out (although I'd rather just use to_char()..)
For now, I think we should keep the change simple. If PostgreSQL offers
a DATETIME WITHOUT TIME ZONE, I think we should use that, and rely on
the output from it.
In the end, we should keep the DATETIME WITHOUT TIME ZONE, but use the
right functions to get output from the database we can depend on.
Any thoughts on this?
Ilja
Ilja Booij wrote:
Hi,
I agree that we should use some kind of scheme to get a fixed format
from the database. I wonder why to_char() and to_date() are not in
MySQL. They are part of SQL92, aren't they?
Is it possible to include the database functions, from the URL Aaron
provided, in an installation script? If so, we could use this.
Ilja
Aaron Stone wrote:
Using explicit ANSI SQL functions should give us better portability to
whatever default format the database wants to use. I think it's quite
bad form
that we're currently assuming that the database will store and return the
format that we want simply because we remembered to use the right
column type.
SELECT to_char(datecolumn, 'YYYY-MM-DD HH24:MI:SS') FROM whatevertable;
UPDATE whatevertable SET datecolumn = to_date('1999-02-01
00:00:00','YYYY-MM-DD HH24:MI:SS');
In MySQL, to_date and to_char do not exist. date_format is used
instead. At
the moment, we implement lots of queries (all of them, perhaps) in the
midlevel and so it would probably be a pain to push the date stuff
down into
the database specific lowlevel. So as an alternative, we could use MySQL
specific date functions in the midlevel and then use a series of stored
functions to emulate the MySQL stuff in PostgreSQL and elsewhere. See
here:
http://www.xach.com/aolserver/mysql-functions.sql
Aaron
""Matthew T. O'Connor"" <matthew@zeut.net> said:
Paul F. De La Cruz wrote:
On Tue, Feb 24, 2004 at 09:16:59PM -0000, Aaron Stone wrote:
As long as this doesn't cause pre-7.1 version of PostgreSQL to
choke, the
change should definitely be made before 2.0rc3!
I think it would make the pre-7.1 versions of PostgreSQL choke as they
either only have timestamp (without the additional 'with/without time
zone' or
use some other strange format. I'm thinking that this may be a problem
that's
been around for awhile and just wasn't noticed since PostgreSQL seems
to give
dates different from what date_sql2imap is looking for. I'm no PostgreSQL
expert though so if someone knows otherwise concerning older PG
versions, then
by all means speak up.
I don't see a big problem dropping support for pre-7.1. 7.0.x is
very old. Anyone who is actually interested in running dbmail would
want a newer postgresql. Also, the number of users still on
postgresql that old is very small, see this little survey from the
postgresql website:
http://www.postgresql.org/survey.php?View=1&SurveyID=14
I'm not exactly sure what date that survey was taken, but based on
the 7.4 DEV that is listed I would assume it is at least before 7.4
was released which was back in November.
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
