Thread: SET DATESTYLE to time_t style for client libraries?

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
 

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

>         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

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
 

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