Results 1 to 14 of 14

Thread: timezone help

  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: timezone help

    Setup: 9i DB

    I have spent 50+ hours researching and dealing with OTN Oracle Forms (that has been wasted time). Sorry just needed to vent some.

    What I need: a select statement that will give me the timezone abbreviation (CST, CDT, PST, PDT, etc.) that reflects the timezone the current "user" is in. i.e. If I ran it today I would see 'CDT', if I ran it in January (non-daylight saving time) I would see CST.

    Oracle gives all kinds of wonderful examples but they all use a hard coded string as a time input instead of something like SYSDATE etc.

    My latest attempts have revolved around
    SQL> alter session set nls_timestamp_tz_format = 'TZD';

    Session altered.

    SQL> select sessiontimezone from dual;

    SESSIONTIMEZONE
    --------------------------------------------------------
    -05:00

    This is all well and good (-05:00 is correct for CDT) I would just like to see 'CDT'.

    I have tried several permutations of to_timestamp_tz all to no useful affect. I find this result particularly troubling.
    SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT = 'TZD';

    Session altered.

    SQL> select current_timestamp from dual;

    CURRENT_TIMESTAMP
    --------------------------------------------------------

    (the above line is blank). Maybe that tells you all something that can help me.


    I am so frustrated by the issue I can not even rationally think about it.

    I throw myself upon your mercy.
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    the USER or the SERVER location TZ?

    you SAY user in the beginning, but your examples are retrieving the TZ
    from the server.

    What if the server is in NYC, but the user is in Los Angeles?
    What TZ do you want in that situation>?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by The_Duck
    the USER or the SERVER location TZ?

    you SAY user in the beginning, but your examples are retrieving the TZ
    from the server.

    What if the server is in NYC, but the user is in Los Angeles?
    What TZ do you want in that situation>?
    oops!
    I was wrong. sessiontimezone

    sorry.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Oh Great Poobah. I frankly do not care. If I get get either one I can hopefully figure out the other. Actually as stated I would prefer user. I have other issues with the dbtimezone return but do not care about that at this point.

    Thanks for looking into this.
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    This is a common problem.
    I get my timezone from the server side since I could not find a solution (just like you!). Very frustrating.

    The only thing you could do is to create a table linking:
    V$timezone_names
    and
    all possible values from sessiontimezone

    ie:
    EST = -5:00
    EDT = -4:00
    etc.


    pain in the ass however.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I have thought of that but here is the issue I see

    is -05:00 EST or CDT (i.e. it can be both just depends whether you are in daylight saving time or not).

    I find it hard to believe that Oracle has not given a solution for this. That is probably why I am so frustrated. I would not think it would be this hard.

    Any one know how to get the TZ from a Windows (W2K) platform?
    NOTE: Please disregard the label "Senior Member".

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    I just did a thorough search on some forums and on Google and couldn't find ANYTHING. MAN!

    Windows sux.

    Thank God for Unix and Linux!

    I guess the question would be:

    1. Why do you need the text timezone? 'CDT' 'CST' etc?
    Do you need to display this or something?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    A customer requirement is to display the TZ where the server is running.

    I am starting another thread on a related topic. I would appreciate your input there also.
    NOTE: Please disregard the label "Senior Member".

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    based on that requirement you will need a TIMEZONE table.
    We do this on our database.

    Basically we associate timezones to each customer:
    You could associate each customer against the v$timezone table perhaps
    or jsut create your own.


    I also found this perhaps handy function:
    PHP Code:
    SELECT TZ_OFFSET('US/Eastern'FROM DUAL;

    TZ_OFFS
    -------
    -
    04:00 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    from http://asktom.oracle.com

    ops$tkyte@8i> create or replace and compile java source
    named "TZ"
    2 as
    3 import java.net.*;
    4 import java.io.*;
    5 import java.util.*;
    6 import java.text.*;
    7 public class TZ
    8 {
    9 static public void java_get_timezone( String[] p_timezone )
    10 {
    11 Date d = new Date();
    12 DateFormat df2 = new SimpleDateFormat( "z" );
    13
    14 df2.setTimeZone( TimeZone.getDefault() );
    15 p_timezone[0] = df2.format(d);
    16 }
    17 }
    18 /

    Java created.

    ops$tkyte@8i>
    ops$tkyte@8i> create or replace
    2 procedure get_timezone( p_timezone out varchar2 )
    3 as language java
    4 name 'TZ.java_get_timezone( java.lang.String[] )';
    5 /

    Procedure created.

    ops$tkyte@8i>
    ops$tkyte@8i> declare
    2 tz varchar(25);
    3 begin
    4 get_timezone( tz );
    5 dbms_output.put_line( tz );
    6 end;
    7 /
    EDT

    PL/SQL procedure successfully completed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Dang! You go girl! It looks like this is my only option.

    I had searched AskTom and countless others. At most I had remembered to search for "time zone" and "timezone". Evidently at AskTom I just did "time zone".

    Thanks.

    Another example of why we have forums instead of just search engines, search engines are useless if you do not search for the correct thing.
    NOTE: Please disregard the label "Senior Member".

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    So how does this work?

    Show me show me show me.


    BTW - I think this proves that sometimes all someone needs is only a fish.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Uh Duck. Read the thread. She shows you how (from AskTom). Works great.
    NOTE: Please disregard the label "Senior Member".

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by Todd Barkus
    Uh Duck. Read the thread. She shows you how (from AskTom). Works great.
    What happened?
    Where am I?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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