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

> 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().

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.

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

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.

Have fun with it...

                        - Tom