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"" <[email protected]> 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
> >>> [email protected]
> >>> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >>>
> >>
> >>
> >>
> >>
> > _______________________________________________
> > Dbmail-dev mailing list
> > [email protected]
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> _______________________________________________
> Dbmail-dev mailing list
> [email protected]
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
--
