Re: [HACKERS] Re: [QUESTIONS] Anything like strftime() for PostgreSQL?

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
:)