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

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

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