Thread

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

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-04-23T03:00:35Z

   > 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
   
   

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

   Brett McCormick <brett@work.chicken.org> — 1998-04-24T01:29:34Z

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

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

   David Gould <dg@illustra.com> — 1998-04-24T02:13:18Z

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