PostgreSQL JDBC - Custom types in Postgres, JDBC

Author

Custom types in Postgres, JDBC

Christian Cryder

2005-07-20, 8:24 pm

Here's a question. Let's say I wanted to create a custom datatype in
Postrgres - something like a timezone2 which would persist the data as
millis. Does anyone have any pointers on where one would start in
thinking about this?

I am pretty sure that I could implement something in our statement
wrapper classes that would allow me to convert timestamps to millis
values and then save them as bigints rather than timestamps. And I
could probably create a couple of stored proc functions on the server
for easily displaying such millis as timestamps, and querying against
them via a timestamp string.

So I guess what I am asking is - how would one actually do it at a
lower level - defining a new type within postgres, and then having
that mapped to a new custom type (which my wrapper classes could then
convert to java.sql.Timestamps). I realize this is more than just a
JDBC question, but JDBC certainly plays a role in it.

Any thoughts on whether this is a good idea or a bad one? Any pointers? Caveats?

Thanks,
Christian

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Dave Cramer

2005-07-20, 8:24 pm

On 20-Jul-05, at 4:52 PM, Christian Cryder wrote:

> Here's a question. Let's say I wanted to create a custom datatype in
> Postrgres - something like a timezone2 which would persist the data as
> millis. Does anyone have any pointers on where one would start in
> thinking about this?
>
> I am pretty sure that I could implement something in our statement
> wrapper classes that would allow me to convert timestamps to millis
> values and then save them as bigints rather than timestamps. And I
> could probably create a couple of stored proc functions on the server
> for easily displaying such millis as timestamps, and querying against
> them via a timestamp string.

also look at http://joda-time.sourceforge.net/
for an implementation of date/time for java that might be better
>
> So I guess what I am asking is - how would one actually do it at a
> lower level - defining a new type within postgres, and then having
> that mapped to a new custom type (which my wrapper classes could then
> convert to java.sql.Timestamps). I realize this is more than just a
> JDBC question, but JDBC certainly plays a role in it.
have a look at this

http://www.postgresql.org/docs/8.0/...createtype.html

>
> Any thoughts on whether this is a good idea or a bad one? Any
> pointers? Caveats?
>
> Thanks,
> Christian
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that
> your
> message can get through to the mailing list cleanly
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links