-
Timezone
We are planning to move our application on a centralized system, which can be used by people worldwide. The need is to track and use time zones for each customer.
The application was not originally built with this requirement in mind and uses a few 1000s of sysdates internally. Also "DATE" is used commonly as the datatype. Ideally what we are looking for is a possibility for overriding the "sysdate" function. Is this possible?
Any other suggestions will also be appreciated.
Thanks in advance.
DB details
Oracle v 10g (10.2.0.3.0)
Application is java based.
-
All you need to do is set your server time to UTC (GMT) and set appropriate time zone parameter in the server.
You cannot overwrite sysdate fn. SYSDATE gets the time from the server only.
Also "DATE" is used commonly as the datatype - What does it mean?
Tamil
-
You can set a fixed sysdate, but that's really for testing purposes not for operational stuff.
-
"The need is to track and use time zones for each customer. "
Oracle DATE datetypes have seven bytes. They don't have 'room' for a time zone.
If you need to store and process timezone information, then you will need to revisit the database structures and application. There's no magic wand.
-
Hi,
Thanks for your replies.
Quote -
Also "DATE" is used commonly as the datatype - What does it mean?
tamilselvan, the application predominantly uses "DATE" datatype and as rightly pointed out by gamyers, it is not equipped to store timezone information.
Application uses the sysdate function to audit logs and transaction details, hence fixed date, also will not help.
Change in database structure to use something like "timestamp with timezone" or "timestamp with local timezone" will require massive effort.
Is it possible to capture timezone information, while calling "sysdate" function (since the application code is heavily dependent on "sysdate")
thanks.
-
How about adding a column (timezone) in tables and populate it via trigger?
Tamil
-
I think you'll need to replace SYSDATE with CURRENT_TIMESTAMP
-
I agree with gamyers. We have tried and explored a few options, but the best possible solution would be to go about a code change and to use something like "CURRENT_TIMESTAMP" or "LOCALTIMESTAMP"
This will use a time according to TIME_ZONE set for the session.
I have a doubt, as to what is the difference betwen these two functions ("CURRENT_TIMESTAMP" or "LOCALTIMESTAMP") and which one is be better option in this case.
Thanks.
-
The difference between these two functions is that the localtimestamp function returns a TIMESTAMP value while the current_timestamp function returns a TIMESTAMP WITH TIME ZONE value.
SQL>
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------
07-FEB-07 11.19.37.370346 AM -05:00
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
--------------------------------------------
07-FEB-07 11.19.55.475805 AM
SQL>
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|