Thread

1. SET DATESTYLE to time_t style for client libraries?

   Adam Haberlach <adam@newsnipple.com> — 2002-01-01T22:26:47Z

   	So I discovered today that pgdb follows in the traditional style of
   carrying timestamp and most other time fields through to the user as
   text strings, so I either need to have all my queries do some gymnastics
   to have the server format my time information in a way that is printable
   or can be handled by my client code or whatever.
   
   Is there a better way?  I was thinking that if there was a way to set a
   datestyle that would just emit the seconds since the Unix epoch, I could
   kick them into the python time module's functions for easier formatting,
   and it would give all clients a more standardized way to deal with time
   by letting them get the 'raw' values and handle them locally.
   
   Is this a good, bad, or old idea?  Should I spend some time trying to
   patch my local system for testing?
   
   -- 
   Adam Haberlach      | Who buys an eight-processor machine and then watches 30
   adam@newsnipple.com | movies on it all at the same time?  Beats me.  They told
                       | us they could sell it, so we made it.
                       |        -- George Hoffman, Be Engineer
   
   

2. Re: SET DATESTYLE to time_t style for client libraries?

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-01-03T15:05:24Z

   Adam Haberlach <adam@newsnipple.com> writes:
   > Is there a better way?
   
   	SELECT EXTRACT(epoch FROM timestamp-value)
   
   			regards, tom lane
   
   

3. Re: SET DATESTYLE to time_t style for client libraries?

   Thomas Lockhart <lockhart@fourpalms.org> — 2002-01-03T15:44:50Z

   >         So I discovered today that pgdb follows in the traditional style of
   > carrying timestamp and most other time fields through to the user as
   > text strings, so I either need to have all my queries do some gymnastics
   > to have the server format my time information in a way that is printable
   > or can be handled by my client code or whatever.
   
   Right. Though the available styles *should* cover common usage, and
   ISO-8601 is not a bad way to go imho.
   
   > Is there a better way?  I was thinking that if there was a way to set a
   > datestyle that would just emit the seconds since the Unix epoch, I could
   > kick them into the python time module's functions for easier formatting,
   > and it would give all clients a more standardized way to deal with time
   > by letting them get the 'raw' values and handle them locally.
   
   Hmm. If the Python module has any date/time input routines, it *should*
   be easy to ingest ISO-formatted dates. No? How about one of the other
   available styles? If nothing else, you could go through to_char() to
   format the date exactly as Python needs to see it (or directly for
   display on your client apps). date_part('epoch'...) could get you Unix
   system time, but that would last on my list...
   
                        - Thomas
   
   

4. Re: SET DATESTYLE to time_t style for client libraries?

   Adam Haberlach <adam@newsnipple.com> — 2002-01-03T20:32:44Z

   On Thu, Jan 03, 2002 at 03:44:50PM +0000, Thomas Lockhart wrote:
   > >         So I discovered today that pgdb follows in the traditional style of
   > > carrying timestamp and most other time fields through to the user as
   > > text strings, so I either need to have all my queries do some gymnastics
   > > to have the server format my time information in a way that is printable
   > > or can be handled by my client code or whatever.
   > 
   > Right. Though the available styles *should* cover common usage, and
   > ISO-8601 is not a bad way to go imho.
   
   	Ok...
   
   > > Is there a better way?  I was thinking that if there was a way to set a
   > > datestyle that would just emit the seconds since the Unix epoch, I could
   > > kick them into the python time module's functions for easier formatting,
   > > and it would give all clients a more standardized way to deal with time
   > > by letting them get the 'raw' values and handle them locally.
   > 
   > Hmm. If the Python module has any date/time input routines, it *should*
   > be easy to ingest ISO-formatted dates. No? How about one of the other
   > available styles? If nothing else, you could go through to_char() to
   > format the date exactly as Python needs to see it (or directly for
   > display on your client apps). date_part('epoch'...) could get you Unix
   > system time, but that would last on my list...
   
   	I'll look into getting it to ingest dates, but it seems wasteful to have
   the server take its internal reprentation, pretty-format it into a nice
   human-readable representation to send to the client, and then have the client
   parse that into something it can deal with internally.  While it is a fairly
   minor performance issue, it seems there are a lot of chances for things to
   go wrong.
   
   	I've already had to hack my python libs a bit to make the money
   type work correctly.  It takes the incoming text, removes '$' and ',' and
   then tries to convert it into a float.  In the case of negative values, it
   will blow up because there are "()" around the value.  I'll submit a patch
   if anyone is interested.
   
   ...I assume that the ISO-8601 representation itself won't be changing, but
   time is silly and there's a lot of edge cases.  It'd be nice to have a way
   to reliabily tell the server "Give me standardized raw values, I'll sort
   things out on my end."  Of course, this may already be happening within
   the C libraries and I'm not seeing them inside python.  I'll look around
   a bit more.
   
   -- 
   Adam Haberlach      | Who buys an eight-processor machine and then watches 30
   adam@newsnipple.com | movies on it all at the same time?  Beats me.  They told
                       | us they could sell it, so we made it.
                       |        -- George Hoffman, Be Engineer
   
   

5. Re: SET DATESTYLE to time_t style for client libraries?

   Jason Earl <jason.earl@simplot.com> — 2002-01-03T22:13:29Z

   I would suggest taking a look at the mxDateTime package if you want to
   manipulate dates in Python.
   
   Adam Haberlach <adam@newsnipple.com> writes:
   
   <snip>
   
   > 
   > 	I'll look into getting it to ingest dates, but it seems
   > wasteful to have the server take its internal reprentation,
   > pretty-format it into a nice human-readable representation to send
   > to the client, and then have the client parse that into something it
   > can deal with internally.  While it is a fairly minor performance
   > issue, it seems there are a lot of chances for things to go wrong.
   
   That's a good point.  On the other hand, I trust the PostgreSQL folks
   to know more about all of the wacky time edge cases than I do.  I know
   that I am not particular excited about using raw time_t values.
   
   > 	I've already had to hack my python libs a bit to make the
   > money type work correctly.  It takes the incoming text, removes '$'
   > and ',' and then tries to convert it into a float.  In the case of
   > negative values, it will blow up because there are "()" around the
   > value.  I'll submit a patch if anyone is interested.
   
   Why not simply use the numeric type?  I thought the money type was
   deprecated.
   
   > ...I assume that the ISO-8601 representation itself won't be
   > changing, but time is silly and there's a lot of edge cases.  It'd
   > be nice to have a way to reliabily tell the server "Give me
   > standardized raw values, I'll sort things out on my end."  Of
   > course, this may already be happening within the C libraries and I'm
   > not seeing them inside python.  I'll look around a bit more.
   
   mxDateTime is your friend.
   
   Jason