Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2015
    Posts
    5

    Unanswered: capture the live transactions time of Respective Countries

    Dear Experts,
    NLSRTL 11.2.0.4.0 Production
    Oracle Database 11g Enterprise Edition 11.2.0.4.0 64bit Production
    PL/SQL 11.2.0.4.0 Production


    capture the live transactions time of Respective Countries

    we have designed Application based on Asia Timing,If our application are used by different countries (America,Sweden..etc)
    It captures Asia country Timezone.

    We want to capture the respective country Timezone if application are used by their own country.

    Please Help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In my opinion, you are making more work for yourself and the application's users than necessary.

    Store all dates and times in the database in UTC. Report all dates and times to the user a local time (for that user).

    UTC time is easy to get on every platform that I've used, including (but not limited to) Unix, Linux, zOS, iSeries, Windows (since Win-95 anyway), Mac (a Unix derivative), Android, iOS, etc. UTC time is readily available in every client/server database platform that I can think of, but there might be a few extremely esoteric exceptions to this statement. In short, you can always get UTC time, and it rarely takes much more effort than to get local time.

    Translating UTC to local time is generally very easy and is always possible too. In most cases, the tools used to generate UI/UX provide the UTC to local time conversion as a simple setting or configuration choice. Reporting tools also generally provide this functionality. You may have specific problems or objections, but you'll need to raise them before we can address them.

    By storing all of the date and time information as UTC, you neatly avoid all of the plagues associated with time conversions inside the database. You don't have to worry about whether a specific 03:00 comes before or after a different 23:30 (due to time zone differences), every date and time is stored in a single standard time zone. This keeps indexing, sorting, comparisons, etc. clean and simple as well as logically consistent.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Similar discussion going on on the DB2 forum, you might want to read through that for some further clarification on your timestamps.

    http://www.dbforums.com/showthread.p...andling-of-DST
    Dave

  4. #4
    Join Date
    Aug 2015
    Posts
    5
    Thank you Pat Pat Phelan,can you give simple Example.

    what database and session timezone i need to set?

    CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0),
    orderdate2 TIMESTAMP(0) WITH TIME ZONE);

    INSERT INTO global_orders VALUES ( '14-09-15 04:02:21 PM',
    '14-09-15 04:02:21 PM ASIA/CALCUTTA');

    Now, It inserts Asia/CALCUTTA timing in global_orders table.I want it should display America/New_York Timing.

    how to make INSERT as UTC?

    Please help.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without a lot more insight into how your server is configured, how the data has been entered, etc. I'm reluctant to prescribe any given fix.

    Your user name is "NEW DB2 USER" but you are posting in the Oracle forum. I don't think that the syntax you posted is quite compatible with either database engine (but haven't tested to prove that). I'm not even completely sure which database engine you are using!

    There are just too many possible problems, you need to either hire a consultant or work through the plan by understanding what you have, what you want, and what choices you have for getting from one to the other. This is a much more complex problem than can be addressed in a forum discussion because it requires intimate understanding of your database, schema, data, and the handling for each of them from installation through the end of the conversion from Calcutta to world time.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Aug 2015
    Posts
    5
    Thank you pat

    Now,My database configured as SET timezone = 'UTC'

    alter database set time_zone='UTC'

    As we know UTC does not get adjusted for daylight savings?

    How to handle when DST in America/Newyork ?

    create table date_table (
    time_stamp_tz TIMESTAMP WITH TIME ZONE,
    time_stamp_ltz TIMESTAMP WITH LOCAL TIME ZONE);


    insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP);

    time_stamp_tz time_stamp_ltz
    16-09-15 05:59:31.574221000 PM +05:30 16-09-15 12:29:31.574221000 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •