Re: [HACKERS] Re: [QUESTIONS] Anything like strftime() for PostgreSQL? - Mailing list pgsql-hackers

>
> On Thu, 23 April 1998, at 03:00:35, Thomas G. Lockhart wrote:
>
> > > Even if it takes an argument of datetime?  I'm preparing this for
> > > contrib, what should I do?  Basically, there will be one function:
> > > date_format(text, datetime) returns text, which is an implementation
> > > of strftime.  I use mktime, which is used elsewhere in the code, but
> > > only inside of #ifdef USE_POSIX_TIME blocks.  I don't beleive this
> > > function to be portable, but it usually has an equiavalent of
> > > timelocal() on other platforms.  Any suggestions?  I'm autoconf
> > > illiterate.
> >
> > It's not an autoconfig problem, it's a problem with trying to use Unix
> > system times to do this. mktime assumes the limited range of 32-bit Unix
> > system time as input, and datetime has much higher precision and much
> > wider range. So, you can do two approaches:
> >
> > 1) check the year field of the datetime input after it is broken up into
> > the tm structure by datetime2tm() and throw an elog(ERROR...) if it is
> > out of range for mktime() or the non-posix equivalent. If it is within
> > range, just lop 1900 off of the year field and call mktime().
>
> How do I handle the non-posix equivalent?  is timelocal guaranteed to
> be there if USE_POXIX_TIME isn't defined?  I'd like this to be
> portable (which is why I mentioned autoconf)
>
> >
> > or
> >
> > 2) implement your own formatter which can handle a broad range of years.
> >
> > As you might guess, (2) is preferable since it works for all valid
> > datetime values. You will also need to figure out how to handle the
> > special cases "infinity", etc.; I would think you might want to pass
> > those through as-is.
>
> I agree.
>
> >
> > Using datetime2tm() you already have access to the individual fields, so
> > writing something which steps through the formatting string looking for
> > relevant "%x" fields is pretty straight forward. Don't think that
> > mktime() does much for you that you can't do yourself with 50 lines of
> > code (just guessing; ymmv :).
>
> Yeah, unfortunately strftime (mktime is for getting the wday and yday
> values set correctly) has locale support, and quite a bit of options.
>
> >
> > I would also think about implementing the C code as "datetime_format()"
> > instead which would use the text,datetime argument pair, and then
> > overload "date_format()" using an SQL procedure. That way you can use
> > either additional C code _or_ just SQL procedures with conversions to
> > implement the same thing for the other date/time data types timestamp
> > and abstime.
>
> I'll do that..
>
> >
> > Have fun with it...
> >
>
> Nah, I just want to get it out there.  I have fun stuff to move on to
> :)
>

Consider stealing one of the date manipulation packages from the Perl CPAN
archive. One of them (can't remember which right now) has a full set of
date formatting, parseing, and arithemetic routines in C.

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - Linux. Not because it is free. Because it is better.