SET DATESTYLE to time_t style for client libraries?

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