db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: how to set the timezone of CURRENT_TIMESTAMP to GMT?
Date Mon, 08 Feb 2010 10:07:57 GMT
Den 02/08/2010 10:40 AM, skrev Erin Drummond:
> Hi,
> 
> I have a table in a database that looks like this: CREATE TABLE
> Data(RowID CHAR(16) FOR BIT DATA PRIMARY KEY,Value LONG VARCHAR NOT
> NULL,LastUpdated TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP)

The proper solution would be to use the datatype TIMESTAMP WITH TIMEZONE for the
LastUpdated column, but it's regrettably not implemented in Derby.

> As you can see, the LastUpdated field defaults to CURRENT TIMESTAMP.
> However, this value is always my local timezone (GMT+12). How can I
> automatically convert it to GMT using a builtin database function? I
> am working in GMT timestamps because it is not possible to use the
> timezone of the server for the timestamps because there is no server
> (a separate copy of the database is stored in several nodes all over
> the world, and it replicates the data based on timestamp (which is why
> the timestamp must be in GMT for it to be accurate)). I want to avoid
> doing this at the application level because I have a lot of code that
> omits the LastUpdated field when creating a new record, so I would
> like to set it automatically.

I (using Linux) would have run the database with the environment variable TZ=GMT.

> 
> Can someone help me?
> 
> Thanks,
> Erin


-- 
Bernt Marius Johnsen, Staff Engineer
Database Group, Sun Microsystems, Trondheim, Norway


Mime
View raw message