Re: Date fields usage with DB

Bertrand Mansion wrote: > > le 21/05/02 16:46, Nicolas Hoizey à nhoizey@php.net a écrit : > > > Hi DB users/hackers, > > > > as I already said a few weeks ago, we are trying to hack SPIP (a CMS) > > with PEAR DB so that it works on any db. > > > > I have a few questions about date and time fields and functions usage > > with PEAR DB. > > > > I have to manipulate such data with unique queries in a project that > > has to be compatible with several backends, the main being MySQL, > > PostgreSQL, Oracle and SQL Server. > > > > I can have some differences in the field types, but I need of course > > to get and set the same data to and from PHP. > > > > I wonder if anybody has already established a list of cross db field > > types and functions. > > > > Here are the main usages I will have : > > > > - dates possibly before 1970 and after 2038 > > - usual dates manip: add, sub, interval, etc > > - "fuzzy" dates: 2002-10-00 for october 2002 for example > > - system date and time: "now()" in MySQL, "now" in PostgreSQL, etc > > - ... > > > > I think there are some of these things that have no direct solution, > > but this may be done though an emulation as the sequences. > > > > So my question is: how can I do all this? ;) > > Forget it : in Oracle you have to use TO_CHAR(), TO_DATE() and in MySQL, you > need DATE_FORMAT() or something like that... > > You'd better go with timestamps. At least they are treated the same > everywhere and php can handle them quite well. > > Well, that's what I do now, since I lost a lot of time trying to code > converting functions for other projects. > From time to time I do have the same problems. I'll be glad to hear approaches to that problem. Tomas V.V.Cox