Timezone conversion in PLSQL

sysdateとtimeをESTのような特定のタイムゾーンに変換する必要があります。現在のタイムゾーンを想定できません。

これをplsqlで変換する方法は?私を助けてください。sqlオラクルplsqlplsqldeveloper共有この質問を改善するフォローする2012年4月26日11:18に編集ウィノイド1,11622つのゴールドバッジ10シルバーバッジ10個2424個のブロンズバッジ2012年4月26日7:21に 尋ねたSuvonkar2,2721111個のゴールドバッジ3030枚のシルバーバッジ4242個のブロンズバッジ

  • わかりました、あなたはsysdateを持っています。しかし、私はあなたが何を得たいのか理解していません。例を書いてください。 – ワシリーコマロフ2012 年4月26日8:03

コメントを追加

7つの答え

アクティブ最古投票7

TIMESTAMP WITH TIME ZONE(などsystimestamp)があると仮定すると、AT TIME ZONE構文を使用できます。たとえば、現在のsystimestamp時刻を取得して、異なるタイムゾーン名を指定することにより、UTC(GMT)、東部、および太平洋のタイムゾーンに変換できます。

SQL> ed
Wrote file afiedt.buf

  1  select systimestamp at time zone 'UTC' current_time_in_utc,
  2         systimestamp at time zone 'Us/Eastern' current_time_in_est,
  3         systimestamp at time zone 'US/Pacific' current_time_in_pst
  4*   from dual
SQL> /

CURRENT_TIME_IN_UTC
---------------------------------------------------------------------------
CURRENT_TIME_IN_EST
---------------------------------------------------------------------------
CURRENT_TIME_IN_PST
---------------------------------------------------------------------------
26-APR-12 05.36.11.802000 PM UTC
26-APR-12 01.36.11.802000 PM US/EASTERN
26-APR-12 10.36.11.802000 AM US/PACIFIC

共有この答えを改善するフォローする2012年4月26日17:37に 回答ジャスティン洞窟208k2020個のゴールドバッジ338338個のシルバーバッジ358358個のブロンズバッジコメントを追加3

Oracleにはすでにタイムゾーンテーブルがあります。

SELECT tzname, tzabbrev from V$TIMEZONE_NAMES
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

SQLステートメントで使用するEPTの現在時刻を取得します。

select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

Oracleの11gSQLリファレンスは非常に優れており、オンラインで入手できます:http//docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm

次の関数をすばやく調べることができます。これらの関数が役立つと確信しています。

  • current_timestamp
  • dbtimezone
  • localtimestamp
  • trun(日付)
  • sessiontimezone
  • sys_extract_utc
  • systimestamp
  • to_timestamp
  • to_timestamp_tz
  • tz_offsetALTER SESSION SET TIME_ZONE = ‘+ 00:00’; –EPTではなくUTCで時刻が表示されるようになりました

さまざまなタイムゾーンでの現在の日付と時刻

現在の日付と時刻(sysdate)を現地の実勢時刻で返します

 Select sysdate from dual;

select LOCALTIMESTAMP FROM DUAL;

現在の日付をUTCとして返します

select LOCALTIMESTAMP at time zone '+00:00' FROM DUAL

–sysdateとto_dateを使用する代わりに、localtimestamp、to_timestamp、to_timestamp_tzを使用してみてください。これらはsysdateやto_dateに似ていますが、タイムゾーン機能が追加されています。

select LOCALTIMESTAMP at time zone '+00:00' FROM DUAL; –- returns essentially the same as sysdate but in UTC

or 

ALTER SESSION SET TIME_ZONE = 'UTC';
select LOCALTIMESTAMP FROM DUAL –- after setting session time_zone to ‘UTC’ this will now return a UTC timestamp

タイムゾーン変換SQLステートメントで使用するためにUTCで現在の時刻を取得します。

-- Get current time in UTC format and subtract 5 minutes.

    LOCALTIMESTAMP at time zone '+00:00' - 5/1440

-- Trunc the time to eliminate seconds

TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI')

-- Convert to characters then back to datetime. 

to_timestamp(to_char(TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi')

-- Select from dual to show it works. 

select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP at time zone '+00:00' - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

Get the current time in EPT for use in a SQL statement.

    -- Get current time in UTC format and subtract 5 minutes.
    ALTER SESSION SET TIME_ZONE = 'US/Eastern'; -- set to EPT time
    select LOCALTIMESTAMP from dual - 5/1440
-- Trunc the time to eliminate seconds

    TRUNC(LOCALTIMESTAMP - 5/1440, 'MI')

    -- Convert to characters then back to datetime. 
    to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi')

    -- Select from dual to show it works. 
    select to_timestamp(to_char(TRUNC(LOCALTIMESTAMP - 5/1440, 'MI'),'mm/dd/yyyy hh24:mi'),'mm/dd/yyyy hh24:mi') FROM DUAL;

DBおよびセッションのタイムゾーン関数を返す

ALTER SESSION SET TIME_ZONE = '+00:00';  -- you will now see times in UTC instead of EPT
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

ALTER SESSION SET TIME_ZONE = '+00:00';  -- you will now see times in UTC instead of EPT
ALTER SESSION SET TIME_ZONE = 'UTC'; -- set to UTC time same as command above
SELECT DBTIMEZONE , SESSIONTIMEZONE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, systimestamp, sysdate FROM DUAL; -- see the results
ALTER SESSION SET TIME_ZONE = 'US/Eastern'; -- set to EPT time
SELECT DBTIMEZONE , SESSIONTIMEZONE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, systimestamp, sysdate FROM DUAL; -- see the results

SELECT TO_TIMESTAMP_TZ('05/16/2014 11:26:48 -04:00',
   'MM/DD/YYYY HH:MI:SS TZH:TZM') FROM DUAL;


SELECT tzname, tzabbrev from V$TIMEZONE_NAMES where tzabbrev = 'EPT';
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

-- The following example casts a null column in a UNION operation as TIMESTAMP WITH LOCAL TIME ZONE using the sample tables oe.order_items and oe.orders:

SELECT order_id, line_item_id,
   CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
   FROM order_items
UNION
SELECT order_id, to_number(null), order_date
   FROM orders;

日時とタイムゾーン(TO_TIMESTAMP_TZ)

TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONEdatatype.

例:次の例では、文字列をTIMESTAMP WITH TIMEZONEの値に変換します。

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
TO_TIMESTAMP_TZ('1999-12-0111:00:00-08:00','YYYY-MM-DDHH:MI:SSTZH:TZM')

次の例では、サンプルのtablesoe.order_itemsとoe.ordersを使用して、UNION操作のnull列をTIMESTAMP WITH LOCAL TIMEZONEとしてキャストします。

SELECT order_id, line_item_id,
   CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) order_date
   FROM order_items
UNION
SELECT order_id, to_number(null), order_date
   FROM orders;

日付と時刻の形式を設定する

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

現在/ローカルのタイムゾーンを設定する

ALTER SESSION SET TIME_ZONE = '-5:00';
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

現地時間(LOCALTIMESTAMP)

LOCALTIMESTAMPは、セッションタイムゾーンの現在の日付と時刻をデータ型TIMESTAMPの値で返します。この関数とCURRENT_TIMESTAMPの違いは、LOCALTIMESTAMPがTIMESTAMP値を返すのに対し、CURRENT_TIMESTAMPはTIMESTAMP WITH TIMEZONE値を返すことです。

ALTER SESSION SET TIME_ZONE = '-5:00';
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

次のステートメントは、正しいフォーマットマスクを使用して、LOCALTIMESTAMPの戻り値の型と一致します。

    INSERT INTO local_test VALUES
   (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

The code above is required to include the TIME ZONE portion of the return type of the function

現在のタイムスタンプ(CURRENT_TIMESTAMP)

CURRENT_TIMESTAMPは、セッションタイムゾーンの現在の日付と時刻をデータ型TIMESTAMP WITHTIMEZONEの値で返します。タイムゾーンオフセットは、SQLセッションの現在の現地時間を反映しています。

    ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

共有この答えを改善するフォローする2014年5月16日19:42に 回答cadvena73455つのシルバーバッジ1313個のブロンズバッジコメントを追加2

これはどう?

select to_timestamp_tz(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || ' ' || 'FROM_TIME_ZONE', 'YYYY-MM-DD HH24:MI:SS TZR') at time zone 'TO_TIME_ZONE'
from dual;

共有この答えを改善するフォローする2012年4月27日12:48に回答JokkeHeikkilä8981ゴールドバッジ1個88つのシルバーバッジ1616個のブロンズバッジコメントを追加1

これを試して:

CREATE TABLE TIMEZONES (ZONE          CHAR(1) PRIMARY KEY,
                        NAMES         VARCHAR2(25) NOT NULL,
                        OFFSET_HOURS  NUMBER NOT NULL);

次のように入力します。

INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Z', 'GMT', 0);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('N', '-1', -1);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('O', '-2', -2);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('P', '-3', -3);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Q', '-4 EDT', -4);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('R', 'EST CDT', -5);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('S', 'CST MDT', -6);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('T', 'MST PDT', -7);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('U', 'PST', -8);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('V', '-9', -9);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('W', '-10', -10);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('X', '-11', -11);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('Y', '-12', -12);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('A', '1', -1);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('B', '2', -2);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('C', '3', -3);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('D', '4', -4);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('E', '5', -5);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('F', '6', -6);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('G', '7', -7);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('H', '8', -8);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('I', '9', -9);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('K', '10', -10);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('L', '11', -11);
INSERT INTO TIMEZONES (ZONE, NAMES, OFFSET_HOURS) VALUES ('M', '12', -12);

上記を考えると、あなたはそれからすることができます

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) + (tz.OFFSET_HOURS / 24)
  FROM TIMEZONES tz
  WHERE tz.NAMES LIKE '%EDT%';

または

  WHERE tz.ZONE = 'Q'

-4タイムゾーンの現地時間を取得します。

共有してお楽しみください。共有この答えを改善するフォローする2012年4月26日17:28に回答ボブ・ジャービス-モニカを復活させる44.2k88つのゴールドバッジ7272個のシルバーバッジ101101個のブロンズバッジコメントを追加1

以下は、夏時間を考慮せずに現在のEST時間(UTC -5時間)を示します。

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL

夏時間を考慮に入れるには、2つのオプションがあります。

  1. 夏時間の変更が発生する日付を計算する関数を記述します
  2. これらの日付を含むテーブルにデータを入力します

ESTタイムゾーンのみをサポートする必要がある場合は、関数を作成することをお勧めします。それ以外の場合は、タイムゾーンによって異なるため、これらの日付を含むテーブルにデータを入力することをお勧めします共有この答えを改善するフォローする2012年4月28日17:24に編集サティヤジスバート19.5k2121個のゴールドバッジ8989個のシルバーバッジ125125個のブロンズバッジ2012年4月26日8:28に回答ウィノイド1,11622つのゴールドバッジ10シルバーバッジ10個2424個のブロンズバッジ

  • お返事をありがとうございます。DATEADDが無効な識別子であるというエラーが表示されます。 –  Suvonkar 2012 年4月26日8:35
  • ああ、お詫びします。Oracleを使用していることに気づきませんでした。これを試してください。SELECTSYS_EXTRACT_UTC(SYSTIMESTAMP)FROM DUAL; –  weenoid 2012 年4月26日8:40 

コメントを追加0

これまでのすべてのメソッドは、現在のタイムスタンプで作業するときにうまく機能します。しかし、オラクルtz_offsetが一般的な時間オフセットを提供することに気づきました。たとえば、7月には

SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

結果は「-04:00」になります。1月には、同じステートメントの結果が「-05:00」になります。したがって、(現在のシステム時間やセッション時間ではなく)データベースに保存されている日付を変換する場合は、何らかの開発を行う必要があります。

あなたはそれに気付くかもしれません

SELECT TZ_OFFSET('EST') FROM DUAL;

常に「-05:00」を返します。残念ながら、他の米国標準時(中央、山、太平洋)のクイックテストでは、異なる結果が示されました。以下を実行して、自分の目で確かめてください。DSTが有効な間、6月16日に次のことを実行しました。

SELECT TZ_OFFSET('US/Eastern'), TZ_OFFSET('EST'),  TZ_OFFSET('EST5EDT') FROM DUAL;
SELECT TZ_OFFSET('US/Central'), TZ_OFFSET('CST'),  TZ_OFFSET('CST6CDT') FROM DUAL;
SELECT TZ_OFFSET('US/Mountain'), TZ_OFFSET('MST'),  TZ_OFFSET('MST7MDT') FROM DUAL;
SELECT TZ_OFFSET('US/Pacific'), TZ_OFFSET('PST'),  TZ_OFFSET('PST8PDT') FROM DUAL;

-04:00      -05:00      -04:00
-05:00      -05:00      -05:00
-06:00      -07:00      -06:00
-07:00      -07:00      -07:00

結果はややイライラします。簡単なサンプリングから、ほとんどすべてのtz_offsetクエリは、現在の一般的な時間で応答しているように見えます。これは、現在の時刻の変換を処理している場合に役立ちますが、データベースから取得された時刻を処理している場合はフラットになります。

これで、コードを記述して、自分が標準的な時間にいるのか、一般的な時間にいるのかを判断できます。ただし、tz_offset標準時間または日中の時間帯に一貫してを引っ張る方法はありません。

これにより、開発者は、ボブジャービスが上記のように独自のテーブルを作成して、以下のコードで行ったように問題をハックする必要があります。

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

Declare
-- ******** User declarations begin here ******** --




-- ******** User declarations end here ******** --

/*******************  All Declarations of Variables and Functions below this point support Time Zone Conversion (Convert_TZ function) *******************/  
  -- TimeZone Conversion Procedure

  -- User Input (Parameters)
  input_date date := TO_TIMESTAMP_TZ('7/1/2009 18:00','mm/dd/yyyy hh24:mi');  -- Try: LocalTimestamp; or TO_TIMESTAMP('2/1/2009 13:00','mm/dd/yyyy hh24:mi')
  --input_date date := LocalTimestamp;
  input_TZ varchar(3) := 'GMT';  -- Exmaples: EST, EDT or EPT for Eastern Standard, Daylight or Prevailing, respectively.
  output_TZ varchar(3) := 'EPT';  -- 

  -- Variables
  type date_array is table of date;
  return_date date := localtimestamp;
  temp_date date := to_date('10/27/1974 02:00','mm/dd/yyyy hh24:mi');
  type str_array is table of varchar2(10);
  dow_list str_array;

   Function dst_start_stop (input_date DATE) 
        RETURN date_array
    AS
        year_part number(4);
        start_week number(1) := 0;  -- week of month: -1=last, 1=1st, 2=nd, 0=fixed date (like 1974 and 1975)
        stop_week number(1) := 0;   -- week of month: -1=last, 1=1st, 2=nd, 0=fixed date (like 1974 and 1975)
        dst_start date := to_date('01/06/1974 23:59','mm/dd/yyyy hh24:mi');
        dst_stop date := to_date('10/27/1974 23:59','mm/dd/yyyy hh24:mi');
        dst_date date := dst_start;
        dst_msg varchar2(500) := ' ';
        inc_dec number := 0;
        Cnt number(1) := 0;
        dst_dow number(1) := 1;  --  1=Sunday, 2=Monday, etc.
        i number;
        dst_range date_array;
   BEGIN
      dst_range := date_array();
      dst_range.extend(2);
      dst_range(1) := temp_date;
      dst_range(2) := temp_date;
      DBMS_OUTPUT.PUT_LINE('  ** Start: dst_start_stop Func **');
      --insert into dst_range values(dst_start,dst_stop);
      --dst_range(1) := dst_start;
      --dst_range(2) :=  dst_stop;
      year_part := to_number(to_char(input_date,'YYYY'));
      DBMS_OUTPUT.PUT_LINE('      Year: '||year_part);

      -- Determine DST formula based on year of input_date
      If year_part > 9999 Then  --   Invalid TempYear > 9999
          dst_msg := 'N/A.  I can''t guess if DST will be applied after 9999.  Standard Time returned.  ';
          Goto found_start_stop;
      ElsIf year_part >= 2007 Then --   2007 forward.  Latest DST Rules used after 2007.
          dst_msg := '2007 forward: Third National DST Standard.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM second Sunday in March (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  '
          --dst_msg := dst_msg || 'Fall Back 2:00 AM first Sunday in November (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  '
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          dst_start := to_date('03/01/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          start_week := 2; -- 2nd Sunday in March
          dst_stop := to_date('11/01/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := 1;  -- 1st Sunday in November
      ElsIf year_part >= 1987 Then  --   1987 thru 2006.
          dst_msg := '1987 thru 2006: Second National DST Standard.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM first Sunday in April (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM last Sunday in October (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          start_week := 1; 
          dst_start := to_date('04/01/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := -1;  
          dst_stop := to_date('10/31/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
      ElsIf year_part >= 1976 Then  --   1976 thru 1986 OLD DST Rules used 1961 thru 1973.
          dst_msg := '1976 thru 1986: First National DST Standard (resumed after 1974-1975 extended DST trials).  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM last Sunday in April (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM last Sunday in October (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          start_week := -1; 
          dst_start := to_date('04/30/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := -1;  
          dst_stop := to_date('10/31/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
      ElsIf year_part = 1975 Then  --   1975 Trial.
          dst_msg := '1975 Trial of Extended DST.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM Feb 23 (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM Oct 26, the last Sun in Oct (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          dst_start := to_date('02/23/1975 02:00','mm/dd/yyyy hh24:mi');
          dst_stop := to_date('10/26/1974 02:00','mm/dd/yyyy hh24:mi');
          Goto found_start_stop;
      ElsIf year_part = 1974 Then  --   1974 Trial.
          dst_msg := '1974 Trial of Extended DST.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM Jan 6 (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00)).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM Oct 27 (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          DBMS_OUTPUT.PUT_LINE('    '||dst_msg);
          dst_start := to_date('01/06/1974 02:00','mm/dd/yyyy hh24:mi');
          dst_stop := to_date('10/27/1974 02:00','mm/dd/yyyy hh24:mi');
          Goto found_start_stop;
      ElsIf year_part >= 1961 Then  --   1961 thru 1973 First National DST Standard.
          dst_msg := '1961 thru 1973: First National DST Standard.  ';
          --dst_msg := dst_msg || 'Spring Forward 2:00 AM last Sunday in April (skip 02:00:00 through 02:59:59, I.E. skip from 01:59:59 to 03:00:00).  ';
          --dst_msg := dst_msg || 'Fall Back 2:00 AM last Sunday in October (repeat 02:00:00 to 02:59:59, I.E. jump back from 02:59:59 to 02:00:00 once).  ';
          start_week := -1; 
          dst_start := to_date('04/30/'||input_date||' 02:00','mm/dd/yyyy hh24:mi');
          stop_week := -1;  
          dst_stop := to_date('10/31/'||year_part||' 02:00','mm/dd/yyyy hh24:mi');
      ElsIf year_part >=1900 Then  --   DST was applied inconsistently or not at all
          dst_msg := 'N/A.  Before 1961, DST was applied inconsistently across states or not at all.  Standard Time returned.  ';
          Goto found_start_stop;
      ElsIf year_part < 1900 Then  --   Invalid year_part
          dst_msg := 'N/A. DST never active before 1900';
          Goto found_start_stop;
      Else  --   Invalid year_part 
          dst_msg := 'N/A.  Error.  Invalid datetime value.';
          Goto found_start_stop;
      End If;   
      DBMS_OUTPUT.PUT_LINE('  The code specified the following DST rules for the input date ('||input_date||').  '||dst_msg);
      if start_week > 0 then
        DBMS_OUTPUT.PUT_LINE('    Start on '||dow_list(dst_dow)||' #'||start_week||' of '||trunc(dst_start,'W')||'. ');
      else
        DBMS_OUTPUT.PUT_LINE('    Starts '||start_week||' from the last '||dow_list(dst_dow)||' of '||trunc(dst_start,'W')||'. ');
      end if;
      if stop_week > 0 then
        DBMS_OUTPUT.PUT_LINE('    End on '||dow_list(dst_dow)||' #'||stop_week||' of '||trunc(dst_stop,'W')||'. ');
      else
        DBMS_OUTPUT.PUT_LINE('    Ends '||stop_week||' from the last '||dow_list(dst_dow)||' of '||trunc(dst_stop,'W')||'. ');
      end if;
      DBMS_OUTPUT.PUT_LINE('    ');


      /* Apply formula determined above to find dst start and stop times for the year of the input_date.
            This section is skipped if start/stop already determined or indeterminant. 
      */
        -- DstStartDay 
        inc_dec := start_week/abs(start_week);  -- results in +1 or -1
        Cnt := 0; i:=0;
        while (Cnt < abs(start_week) and i<20) loop
          i:=i+1;
          if (to_char(dst_start,'D') = dst_dow) then 
            Cnt := Cnt + 1; 
            --DBMS_OUTPUT.PUT_LINE('    Found '||dow_list(dst_dow))||' '||Cnt||': '||dst_start)
          end if;
          if (Cnt < abs(start_week)) then 
            dst_start := dst_start + inc_dec; 
          end if;
        end loop;
        case inc_dec
          when 1 then
            DBMS_OUTPUT.PUT_LINE('  Spring forward on  '||dow_list(dst_dow)||' #'||Cnt||' of the month: '||dst_start);
          else
            DBMS_OUTPUT.PUT_LINE('  Spring forward on the last'||dow_list(dst_dow)||'of the month: '||dst_start);
        end case;
      -- DstStopDay 
        inc_dec := stop_week/abs(stop_week);  -- results in +1 or -1
        Cnt := 0; i :=0;
        while (Cnt < abs(stop_week) and i <20) loop -- to_char(dst_stop,'D') > 1 loop
          i:=i+1;
          if (to_char(dst_stop,'D') = dst_dow) then 
            dst_stop := dst_stop + inc_dec; 
            Cnt := Cnt + 1; 
          end if;
          if (Cnt < abs(stop_week)) then 
            dst_stop := dst_stop + inc_dec; 
          end if;
        end loop;
        case inc_dec
          when 1 then
            DBMS_OUTPUT.PUT_LINE('  Fall back on  '||dow_list(dst_dow)||' #'||Cnt||' of the month: '||dst_stop);
          else
            DBMS_OUTPUT.PUT_LINE('  Fall back on the last'||dow_list(dst_dow)||'of the month: '||dst_stop);
        end case;
    <<found_start_stop>>
      dst_range(1) := dst_start; 
      DBMS_OUTPUT.PUT_LINE('      dst_range(1): '||to_char(dst_range(1),'mm/dd/yyyy hh24:mi')||' = '||dst_start);
      dst_range(2) := dst_stop;
      DBMS_OUTPUT.PUT_LINE('      dst_range(2): '||to_char(dst_range(2),'mm/dd/yyyy hh24:mi')||' = '||dst_stop);
      DBMS_OUTPUT.PUT_LINE('  ** Finish: dst_start_stop Func **');
      Return dst_range;
   END dst_start_stop;

   Function is_dst_now
        Return boolean
      AS
          --type date_array is table of date;
          dst_range date_array;
          curr_time date := LocalTimestamp;
      Begin
          dst_range := date_array();
          dst_range.extend(2);
          dst_range := dst_start_stop(curr_time);
          If (dst_range(1) <= curr_time and curr_time < dst_range(2))  then 
            DBMS_OUTPUT.PUT_LINE('DST is  active.');
            Return True;
          Else
            DBMS_OUTPUT.PUT_LINE('DST is NOT active.');
            Return False;
          End If;  
   End;

   FUNCTION dst_offset (prevailing_date DATE, dst_start DATE, dst_stop DATE) 
        RETURN number
      AS
            offset_days number :=0;
      BEGIN

          DBMS_OUTPUT.PUT_LINE('  Starting dst_offset sub-function:');
          DBMS_OUTPUT.PUT_LINE('        where (input date, DST start, DST stop) = '||to_char(prevailing_date,'mm/dd/yyyy hh24:mi')
                                ||', '||to_char(dst_start,'mm/dd/yyyy hh24:mi')||', '||to_char(dst_stop,'mm/dd/yyyy hh24:mi'));

          If (dst_start <= prevailing_date and prevailing_date < dst_stop) then 
            offset_days :=1/24;
            DBMS_OUTPUT.PUT_LINE('        input date is between dst start and stop');
          Else
            offset_days :=0;
            DBMS_OUTPUT.PUT_LINE('        input date is not between dst start and stop');
          End If;
          DBMS_OUTPUT.PUT_LINE('        Result: DST Offset days = '||offset_days);
          DBMS_OUTPUT.PUT_LINE('                          hours = '||(offset_days*24)); 
          Return offset_days;
   END dst_offset;

-- Begin  --move this down under the function -- ******************

   FUNCTION Convert_TZ (input_date DATE, input_tz varchar2, output_tz varchar2) 
   RETURN date
   AS
        -- Variables
        input_sz varchar(3) := substr(input_TZ,1,1)||'S'||substr(input_TZ,3,1);
        input_sz varchar(3) := substr(output_TZ,1,1)||'S'||substr(output_TZ,3,1);
        temp_str varchar2(1000);
        dst_range date_array;
        input_dst_offset number := 0;
        input_tz_offset number := 0;
        input_date_st date; --standard time
        gmt_date date;
        output_dst_offset number := 0;
        output_tz_offset number := 0;
        output_date date;
        output_date_pt date; -- prevailing time
        tz_offset_str varchar2(30);
        --orig_nls_date_format varchar2(30) := NLS_DATE_FORMAT;
        --TempYear number(4,0) := to_char(TempDate,'YYYY');  -- or := trunc(PrevailingTime, YYYY);

   BEGIN 

      DBMS_OUTPUT.PUT_LINE('Starting Pl/sql procedure.  ');

      DBMS_OUTPUT.PUT_LINE('Input Date: '||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '||input_tz||'.  ');
      -- Find DST start/stop dates  
      dst_range := date_array();
      dst_range.extend(2);
      dst_range := dst_start_stop(input_date);
      DBMS_OUTPUT.PUT_LINE('DST date range determined.  ');
      DBMS_OUTPUT.PUT_LINE('      dst_range(1): '||to_char(dst_range(1),'mm/dd/yyyy hh24:mi')||' = '||dst_range(1));
      DBMS_OUTPUT.PUT_LINE('      dst_range(2): '||to_char(dst_range(2),'mm/dd/yyyy hh24:mi')||' = '||dst_range(2));

      -- Convert Input Date from input time zone to GMT
        If upper(input_TZ) in ('GMT','UCT') then
            -- If input TZ is GMT, we can skip this conversion!
            DBMS_OUTPUT.PUT_LINE('    Input Time is ('||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '
                                  ||input_tz||' GMT).  No conversion required.  ');
            input_tz_offset := 0;
            input_dst_offset := 0; 
            gmt_date := input_date;
        Else
            -- Convert from local prevailing to local standard time
                -- Get input_dst_offset 
                Case upper(substr(input_TZ,2,1)) 
                  When 'S' then 
                    -- already in standard time, not conversion needed.
                    input_dst_offset := 0; -- duplicative
                    DBMS_OUTPUT.PUT_LINE('Standard time ('||input_tz||') entered; no dst offset.' );
                          --input_tz_offset := input_tz_offset;
                  Else 
                    -- run dst_offset function to convert from prevailing or daylight time to standard time.
                    input_dst_offset := dst_offset(input_date, dst_range(1), dst_range(2));
                    input_date_st := input_date - input_dst_offset; 
                    DBMS_OUTPUT.PUT_LINE('Daylight Saving Time Effective ('||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '
                                          ||input_tz||'); 1 hour offset; input DST offset = '||input_dst_offset);
                    DBMS_OUTPUT.PUT_LINE('        where (input_date, dst_start, dst_stop) = '
                                          ||to_char(input_date,'mm/dd/yyyy hh24:mi')
                                          ||' '||input_tz||', '||dst_range(1)||', '||dst_range(2)||', ');
                    DBMS_OUTPUT.PUT_LINE('        which adjusts '||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '||input_tz
                                          ||' daylight to '||input_date_st||' standard time.  ');
                End Case;

            -- Convert from local standard time to GMT
                SELECT TZ_OFFSET((SELECT max(tzname) FROM V$TIMEZONE_NAMES where tzabbrev = input_TZ)) 
                INTO tz_offset_str 
                FROM DUAL;

                input_tz_offset := ( substr(tz_offset_str,1,3)/24 + substr(tz_offset_str,5,2)/1440 );
                If is_dst_now then 
                  input_tz_offset := input_tz_offset - 1/24;
                End if;
                DBMS_OUTPUT.PUT_LINE('    input_tz_offset (fractional days): '||input_tz_offset||'. ');
                DBMS_OUTPUT.PUT_LINE('    input_tz_offset (hours): '||input_tz_offset*24||'. ');
                gmt_date := input_date_st - input_tz_offset;

        End If;

        -- Convert input date from GMT to requested output time zone
        DBMS_OUTPUT.PUT_LINE('  ');
        DBMS_OUTPUT.PUT_LINE('Starting output_date analysis. ');
        If upper(output_TZ) in ('GMT','UCT') then
            -- If desired output TZ is GMT, we can skip this conversion!
            output_tz_offset := 0;
            output_dst_offset := 0; 
            output_date := gmt_date;
            DBMS_OUTPUT.PUT_LINE('    Requested output format is GMT: ('||to_char(output_date,'mm/dd/yyyy hh24:mi')||' '||output_tz||').  No conversion required.  ');
        Else
            -- Get output_dst_offset 
                Case upper(substr(output_TZ,2,1)) 
                  When 'S' then 
                    output_dst_offset := 0; -- duplicative
                    DBMS_OUTPUT.PUT_LINE('Standard time ('||output_TZ||') entered; no dst offset.' );
                  Else
                    output_dst_offset := dst_offset(gmt_date + output_tz_offset, dst_range(1), dst_range(2));
                End Case;
            -- Convert from GMT to local standard time
                SELECT TZ_OFFSET((SELECT max(tzname) FROM V$TIMEZONE_NAMES where tzabbrev = output_TZ)) INTO tz_offset_str FROM DUAL;
                output_tz_offset := ( substr(tz_offset_str,1,3)/24 + substr(tz_offset_str,5,2)/1440 );
                DBMS_OUTPUT.PUT_LINE('    output_tz_offset (fractional days): '||output_tz_offset||'. ');
                DBMS_OUTPUT.PUT_LINE('    output_tz_offset (hours): '||output_tz_offset*24||'. ');
                If is_dst_now then 
                  output_tz_offset := output_tz_offset - 1/24;
                  DBMS_OUTPUT.PUT_LINE('    tz_offset correction... ');
                  DBMS_OUTPUT.PUT_LINE('      output_tz_offset (fractional days): '||output_tz_offset||'. ');
                  DBMS_OUTPUT.PUT_LINE('      output_tz_offset (hours): '||output_tz_offset*24||'. ');
                End if;

                    output_date := gmt_date + output_tz_offset + output_dst_offset;        
                    DBMS_OUTPUT.PUT_LINE('          gmt_date: '||gmt_date);
                    DBMS_OUTPUT.PUT_LINE('  output_tz_offset: '||output_tz_offset);
                    DBMS_OUTPUT.PUT_LINE(' output_dst_offset: '||output_dst_offset);

                    DBMS_OUTPUT.PUT_LINE('Daylight Saving Time ('||output_TZ||') offset = '||output_dst_offset);
                    DBMS_OUTPUT.PUT_LINE('        where (output_date, dst_start, DST_stop) = '||output_date||', '||dst_range(1)||', '||dst_range(2));
                    DBMS_OUTPUT.PUT_LINE('        which adjusts '||output_date||' standard to '||output_date_pt||' daylight time.  ');
        End If;

        DBMS_OUTPUT.PUT_LINE('Output Date = '||to_char(output_date,'mm/dd/yyyy hh24:mi')||' '||output_tz||'.  ');

    Goto AllDone;

    <<FoundError>>      

    <<AllDone>>
      DBMS_OUTPUT.PUT_LINE('   ');
      DBMS_OUTPUT.PUT_LINE('***    Results     ***');
      DBMS_OUTPUT.PUT_LINE('   ');

      if input_dst_offset <> 0 then
        temp_str := 'daylight saving';
      else
        temp_str := 'standard';
      end if;
      DBMS_OUTPUT.PUT_LINE('    Input Date: '||to_char(input_date,'mm/dd/yyyy hh24:mi')||' '||input_tz||', which falls in  '||temp_str||' time.  ');
      DBMS_OUTPUT.PUT_LINE('    GMT Date: '||to_char(gmt_date,'mm/dd/yyyy hh24:mi')||' UTC/GMT.  ');
      DBMS_OUTPUT.PUT_LINE('    Output Date: '||to_char(output_date,'mm/dd/yyyy hh24:mi')||' UTC/GMT.  ');
      if output_dst_offset <> 0 then
        temp_str := 'daylight saving';
      else
        temp_str := 'standard';
      end if;
      DBMS_OUTPUT.PUT_LINE('    All Done.  Return Value: '||to_char(output_date,'mm/dd/yyyy hh24:mi')||' '||output_tz||'.  ');
      DBMS_OUTPUT.PUT_LINE('   ');
      DBMS_OUTPUT.PUT_LINE('*** End of Results ***');
      DBMS_OUTPUT.PUT_LINE('   ');
      DBMS_OUTPUT.PUT_LINE('LocalTimestamp EPT: '||LocalTimestamp);
      DBMS_OUTPUT.PUT_LINE('LocalTimestamp GMT:'||LOCALTIMESTAMP at time zone '+00:00');

      Return output_date;
   END;  
/*********************** End of Declarations of Variables and Functions for Time Zone Conversion (Convert_TZ function) *********************/  
Begin  
/*********************** Start of Procedural Code for  Time Zone Conversion (Convert_TZ function) *********************/  
     /* DOW list is required for Time Zone Conversion (Convert_TZ function) and should not be deleted. */
      dow_list := str_array();
      dow_list.extend(7);
      dow_list(1) := 'Sun';
      dow_list(2) := 'Mon';
      dow_list(3) := 'Tue';
      dow_list(4) := 'Wed';
      dow_list(5) := 'Thu';
      dow_list(6) := 'Fri';
      dow_list(7) := 'Sat';

     /* Next 2 lines are example of use of Time Zone Conversion (Convert_TZ function). */
      return_date := Convert_TZ (input_date, input_tz, output_tz);
      DBMS_OUTPUT.PUT_LINE('ta-dah! '||return_date);
/*********************** End of Procedural Code for Time Zone Conversion (Convert_TZ function) *********************/  

-- ******** User coded begins here ******** --
End;    

共有この答えを改善するフォローする2014年6月18日23:51に編集Bmo1,2101111個のシルバーバッジ2828個のブロンズバッジ2014年6月18日23:17に回答user37544701コメントを追加0

RTRIM( TO_CHAR( systimestamp at time zone 'GMT', 'DD-MON-YYYY HH24:MI:SS TZR' ))
RTRIM( TO_CHAR( systimestamp at time zone 'EST', 'DD-MON-YYYY HH24:MI:SS TZR' ))
RTRIM( TO_CHAR( systimestamp at time zone 'PST', 'DD-MON-YYYY HH24:MI:SS TZR' ))

output:
   05-NOV-2014 17:20:10 GMT
   05-NOV-2014 12:20:10 EST
   05-NOV-2014 09:20:10 PST

substr('05-NOV-2014 09:20:10 PST', -3, 3)   will return 'PST'

RTRIM (EXTRACT (HOUR from systimestamp ))
output:  17    (notice the 17 is in GMT time)

共有この答えを改善するフォローする2014年11月5日21:31に編集2014年11月5日21:24に回答マークM11ブロンズバッジ1個

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA