Using a specific time zone for timestamp values for Derby

[Dirk Lattermann <dl...@alqualonde.de>]

Derby (and H2), for example, store TIMESTAMP values not as a time offset
from some epoch, but as a local datetime value, without time zone
information.

There are two possibilities to map that by JDBC:
1. to java.util.Date, java.sql.Date, java.sql.Timestamp: the JVM's
system time zone is used to convert the database value to a offset
value from the epoch, so the result is dependent on the system time
zone.

2. you can pass a Calendar object to the JDBC getTimestamp() call and
specify the time zone to use.

According to my observations, it seems Cayenne (and the Derby adapter?)
use the first approach. Is it possible to configure it so that a
calendar with UTC is used? I'd like to have a switchable front end time
zone which and application behaviour which is independent of the system
time zone, so I want to store all timestamps as UTC in the database.

The same would apply for DATE and TIME.

A workaround would be to switch the JVM default time zone to UTC on
application startup, but that is not so nice IMO.

Thank you,
Dirk

Re: Using a specific time zone for timestamp values for Derby

[Dirk Lattermann <dl...@alqualonde.de>]

Am Sat, 25 Mar 2017 17:57:40 +0000
schrieb John Huss <jo...@gmail.com>:

> The jvm time zone should always be set to UTC. It will avoid problems
> and has no real downsides.

true, but not always in the hands of the software developer. It may
lead to conflicts when one library expects UTC while another the local
TZ. It'd be preferrable if there is a way to develop the software in a
way that is not dependent on the JVM time zone. Hence my question.

Re: Using a specific time zone for timestamp values for Derby

[John Huss <jo...@gmail.com>]

The jvm time zone should always be set to UTC. It will avoid problems and
has no real downsides.
On Sat, Mar 25, 2017 at 11:57 AM Dirk Lattermann <dl...@alqualonde.de>
wrote:

> Derby (and H2), for example, store TIMESTAMP values not as a time offset
> from some epoch, but as a local datetime value, without time zone
> information.
>
> There are two possibilities to map that by JDBC:
> 1. to java.util.Date, java.sql.Date, java.sql.Timestamp: the JVM's
> system time zone is used to convert the database value to a offset
> value from the epoch, so the result is dependent on the system time
> zone.
>
> 2. you can pass a Calendar object to the JDBC getTimestamp() call and
> specify the time zone to use.
>
> According to my observations, it seems Cayenne (and the Derby adapter?)
> use the first approach. Is it possible to configure it so that a
> calendar with UTC is used? I'd like to have a switchable front end time
> zone which and application behaviour which is independent of the system
> time zone, so I want to store all timestamps as UTC in the database.
>
> The same would apply for DATE and TIME.
>
> A workaround would be to switch the JVM default time zone to UTC on
> application startup, but that is not so nice IMO.
>
> Thank you,
> Dirk
>

Re: Using a specific time zone for timestamp values for Derby

[Andrus Adamchik <an...@objectstyle.org>]

Correct, Cayenne follows the first approach. You can create a custom ExtendedType (or types) for the appropriate value types to alter this behavior.

I guess I am with John in that the most portable approach is to keep your JVM on UTC. Wonder if introducing a TZ offset as a potential future Cayenne stack property will just create more confusion...

Andrus 


> On Mar 25, 2017, at 7:57 PM, Dirk Lattermann <dl...@alqualonde.de> wrote:
> 
> Derby (and H2), for example, store TIMESTAMP values not as a time offset
> from some epoch, but as a local datetime value, without time zone
> information.
> 
> There are two possibilities to map that by JDBC:
> 1. to java.util.Date, java.sql.Date, java.sql.Timestamp: the JVM's
> system time zone is used to convert the database value to a offset
> value from the epoch, so the result is dependent on the system time
> zone.
> 
> 2. you can pass a Calendar object to the JDBC getTimestamp() call and
> specify the time zone to use.
> 
> According to my observations, it seems Cayenne (and the Derby adapter?)
> use the first approach. Is it possible to configure it so that a
> calendar with UTC is used? I'd like to have a switchable front end time
> zone which and application behaviour which is independent of the system
> time zone, so I want to store all timestamps as UTC in the database.
> 
> The same would apply for DATE and TIME.
> 
> A workaround would be to switch the JVM default time zone to UTC on
> application startup, but that is not so nice IMO.
> 
> Thank you,
> Dirk