Is it possible to use an offset when inserting a timestamp field?
For example, let's say you had a webserver located in NY and someone from
California logged in. In your application you want to capture when they
last signed in. The problem is, if you simply use a timestamp field in
conjunction with an SQL insert statement that has DEFAULT as the parameter,
you will always get the time on the server in NY. What I am looking to do
is to set a field in the user table to indicate an offset (in hours) so
that all inserts could be adjusted correctly. Ideally the insert might
look like :
INSERT INTO sign_on_log VALUES (DEFAULT - 3,'ID2218'). What is the best
way to accomplish this? Is this a case for a stored procedure or Java Code
in the database or would it make sense to write some code to hand calculate
the timestamp value out to the millisecond and then just insert with a
variable instead of DEFAULT. Thanks for any insights.
Date: Tue, 14 Jan 2003 18:10:25 -0500
Subject: Adjusting Timestamps
Content-Type: text/plain; charset="us-ascii"
Xref: forums-1-dub ianywhere.public.general:314
Article PK: 2137
Date: Wed, 15 Jan 2003 14:25:28 -0500
From: Graham Hurst <firstname.lastname@example.org>
Organization: iAnywhere Solutions (a Sybase company)
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
Subject: Re: Adjusting Timestamps
Content-Type: text/plain; charset=us-ascii
Xref: forums-1-dub ianywhere.public.general:311
Article PK: 2128
Usually it's best to use UTC time in the server and have the application convert
to its locale's timezone. FYI, we added some timezone specific features in the
8.0.0 release that might help. The following is from the What's New section for
To permit easier coordination of date/time values across time zones, the
following new features have been added:
CURRENT UTC TIMESTAMP
Adjusts the time zone value by the server's time zone adjustment value.
DEFAULT UTC TIMESTAMP
Specifies a default value for INSERTs and sets updated columns to the value.
returns the number of minutes that must be added to the Coordinated Universal
Time (UTC) to display the new local time.
Allows a connection's time zone adjustment to be modified.