Timezone
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Timezone

  1. #1
    Join Date
    Jan 2007
    Posts
    3

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You can set a fixed sysdate, but that's really for testing purposes not for operational stuff.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    "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.

  5. #5
    Join Date
    Jan 2007
    Posts
    3
    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.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How about adding a column (timezone) in tables and populate it via trigger?

    Tamil

  7. #7
    Join Date
    Feb 2005
    Posts
    158
    I think you'll need to replace SYSDATE with CURRENT_TIMESTAMP

  8. #8
    Join Date
    Jan 2007
    Posts
    3
    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.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
  •  


Click Here to Expand Forum to Full Width