How to save and retrieve these timestamps with the specified timezone using Joda Time, JPA, EclipseLink and Postgres

Question

This seems like it would be a common problem so perhaps I am missing something obvious. Using Joda Time I want to persist dates with a timezone. If I wasn't using JPA I would want to do something like this to save and retrieve the timestamps:

create table test_dates
(
  zone varchar(50),
  ts_with_time_zone timestamp with time zone
);

insert into test_dates values ('UTC', '2012-08-12 12:34:56 UTC');
insert into test_dates values ('MST', '2012-08-12 05:34:56 MST');

select 'UTC in MST', ts_with_time_zone at time zone 'MST' 
    from test_dates where zone = 'UTC';

How then can I save and retrieve these timestamps with the specified timezone using Joda Time, JPA, EclipseLink and Postgres?

I have seen answers using converters but I am not sure how you would specify or use time zones. Sure I could get the UTC time and convert it myself but that defeats the purpose of having the "with time zone" column. Is this possible?

The ideal solution would be if EclipseLink handled JodaTime similar to hibernate.

Answer 1

You may be able to customize your PostgreSQLPlatform to add support for storing the time-zone through JDBC. Your JDBC driver will have to support the time-zone (most likely through a custom type, or a Calendar API).

EclipseLink does have support for time-zones on Oracle, so it should be possible to extend the PostgreSQLPlatform for time-zones as well if the database and JDBC drivers support it.