0

I've worked with Oracle's timestamp with time zone before, but this time I'm getting values as strings and trying to load them to the database.

When converting the string, I end up with a value in the database time zone. This is odd, but workable if it adjusted the time accordingly yet it doesn't. See below:

SELECT TO_CHAR(systimestamp AT TIME ZONE 'Canada/Pacific', 
                                               'HH24:MI:SS Mon DD, YYYY TZD') now_pst
     , TO_CHAR(systimestamp AT TIME ZONE 'Canada/Eastern', 
                                               'HH24:MI:SS Mon DD, YYYY TZD') now_est 
FROM DUAL

which results in:

now_pst                       now_est
09:55:50 Dec 19, 2016 PST     12:55:50 Dec 19, 2016 EST

now to use to_timestamp_tz:

SELECT '09:55:50 Dec 19, 2016 PST' now_pst
 , TO_TIMESTAMP_TZ('09:55:50 Dec 19, 2016 PST', 'HH24:MI:SS Mon DD, YYYY TZD') now_pst_tz
 , '12:55:50 Dec 19, 2016 EST' now_est
 , TO_TIMESTAMP_TZ('12:55:50 Dec 19, 2016 EST', 'HH24:MI:SS Mon DD, YYYY TZD') now_est_tz 
FROM dual

the result:

now_pst                   now_pst_tz                     now_est                   now_est_tz 
09:55:50 Dec 19, 2016 PST 19.12.2016 09:55:50.000 -05:00 12:55:50 Dec 19, 2016 EST 19.12.2016 12:55:50.000 -05:00

what I should get is: NOW_PST_TZ = 19.12.2016 09:55:50.000 -08:00 (not -05:00)

NOW_EST_TZ = 19.12.2016 12:55:50.000 -05:00

I have a lot of expectations that aren't happening here, but mainly that these are supposed to be the same time - one in Canada/Eastern and one in Canada/Pacific. Yet I'm getting 2 different times.

to_timestamp_tz is failing to convert the string to the correct TZD.

as requested:

select sessiontimezone, dbtimezone from dual

result:

SESSIONTIMEZONE DBTIMEZONE 
-05:00          +00:00
Ray DeBruyn
  • 41
  • 1
  • 7
  • Could you run this query: `select sessiontimezone, dbtimezone from dual;` and append it's result to the question ? – krokodilko Dec 19 '16 at 20:34
  • @krokodilko, what do you expect from value `dbtimezone`? – Wernfried Domscheit Dec 19 '16 at 21:22
  • @Wernfried Domscheit `systimestamp` function returns the system date, of the system on which the database resides, with *system time zone*. `dbtimezone` function returns this timezone (system timezone). `sessiontimezone` return time zone of the session. They can be differrent (dbtimezone & sessiontimezone). – krokodilko Dec 20 '16 at 18:37
  • @krokodilko, no `systimestamp` returns time in time zone of database server's operating system, which is not `dbtimezone`, see http://stackoverflow.com/questions/29271224/how-to-handle-day-light-saving-in-oracle-database/29272926#29272926 – Wernfried Domscheit Dec 20 '16 at 18:59

2 Answers2

1

TZD means Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR.

Using TZD without time zone region is ambiguous, check with this query:

SELECT tzabbrev, tzname, TZ_OFFSET(tzname) 
FROM V$TIMEZONE_NAMES 
WHERE tzabbrev in ('PST', 'EST');

TZABBREV    TZNAME  TZ_OFFSET(TZNAME)
EST America/Antigua -04:00
EST America/Atikokan    -05:00
EST America/Cambridge_Bay   -07:00
EST America/Cancun  -06:00
EST America/Cayman  -05:00
EST America/Chicago -06:00
EST America/Coral_Harbour   -05:00
EST America/Detroit -05:00
EST America/Fort_Wayne  -05:00
EST America/Grand_Turk  -05:00
EST America/Indiana/Indianapolis    -05:00
EST America/Indiana/Knox    -06:00
EST America/Indiana/Marengo -05:00
EST America/Indiana/Petersburg  -05:00
EST America/Indiana/Tell_City   -06:00
EST America/Indiana/Vevay   -05:00
EST America/Indiana/Vincennes   -05:00
EST America/Indiana/Winamac -05:00
EST America/Indianapolis    -05:00
EST America/Iqaluit -05:00
EST America/Jamaica -05:00
EST America/Kentucky/Louisville -05:00
EST America/Kentucky/Monticello -05:00
EST America/Knox_IN -06:00
EST America/Louisville  -05:00
EST America/Managua -06:00
EST America/Menominee   -06:00
EST America/Merida  -06:00
EST America/Moncton -04:00
EST America/Montreal    -05:00
EST America/Nassau  -05:00
EST America/New_York    -05:00
EST America/Nipigon -05:00
EST America/Panama  -05:00
EST America/Pangnirtung -05:00
EST America/Port-au-Prince  -05:00
EST America/Rankin_Inlet    -06:00
EST America/Resolute    -06:00
EST America/Santo_Domingo   -04:00
EST America/Thunder_Bay -05:00
EST America/Toronto -05:00
EST Antarctica/Macquarie    +11:00
EST Australia/ACT   +11:00
EST Australia/Brisbane  +10:00
EST Australia/Broken_Hill   +10:30
EST Australia/Canberra  +11:00
EST Australia/Currie    +11:00
EST Australia/Hobart    +11:00
EST Australia/LHI   +11:00
EST Australia/Lindeman  +10:00
EST Australia/Lord_Howe +11:00
EST Australia/Melbourne +11:00
EST Australia/NSW   +11:00
EST Australia/Queensland    +10:00
EST Australia/Sydney    +11:00
EST Australia/Tasmania  +11:00
EST Australia/Victoria  +11:00
EST Australia/Yancowinna    +10:30
EST CST -06:00
EST Canada/Eastern  -05:00
EST EST -05:00
EST EST5EDT -05:00
EST Jamaica -05:00
EST US/Central  -06:00
EST US/East-Indiana -05:00
EST US/Eastern  -05:00
EST US/Indiana-Starke   -06:00
EST US/Michigan -05:00
PST America/Bahia_Banderas  -06:00
PST America/Boise   -07:00
PST America/Creston -07:00
PST America/Dawson  -08:00
PST America/Dawson_Creek    -07:00
PST America/Ensenada    -08:00
PST America/Hermosillo  -07:00
PST America/Inuvik  -07:00
PST America/Juneau  -09:00
PST America/Los_Angeles -08:00
PST America/Mazatlan    -07:00
PST America/Metlakatla  -08:00
PST America/Santa_Isabel    -08:00
PST America/Sitka   -09:00
PST America/Tijuana -08:00
PST America/Vancouver   -08:00
PST America/Whitehorse  -08:00
PST Canada/Pacific  -08:00
PST Canada/Yukon    -08:00
PST Mexico/BajaNorte    -08:00
PST Mexico/BajaSur  -07:00
PST PST -08:00
PST PST8PDT -08:00
PST Pacific/Pitcairn    -08:00
PST US/Pacific  -08:00
PST US/Pacific-New  -08:00

When you use TO_TIMESTAMP_TZ() then you should use TZR with full region name.

Your expression TO_TIMESTAMP_TZ('09:55:50 Dec 19, 2016 PST', 'HH24:MI:SS Mon DD, YYYY TZD') is equivalent to TO_TIMESTAMP_TZ('09:55:50 Dec 19, 2016 '||SESSIONTIMEZONE||' PST', 'HH24:MI:SS Mon DD, YYYY TZR TZD'). It will fail if your current session time zone abbreviation is not in PST/EST.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

Change the final part of the format model from YYYY TZD to YYYY TZR TZD - you are missing TZR in the format model.

SQL> SELECT '09:55:50 Dec 19, 2016 PST' now_pst
  2   , TO_TIMESTAMP_TZ('09:55:50 Dec 19, 2016 PST', 'HH24:MI:SS Mon DD, YYYY TZR TZD') now_pst_tz
  3   , '12:55:50 Dec 19, 2016 EST' now_est
  4   , TO_TIMESTAMP_TZ('12:55:50 Dec 19, 2016 EST', 'HH24:MI:SS Mon DD, YYYY TZR TZD') now_est_tz
  5  FROM dual;

NOW_PST                   NOW_PST_TZ
------------------------- ---------------------------------------------------------------------------
NOW_EST                   NOW_EST_TZ
------------------------- ---------------------------------------------------------------------------
09:55:50 Dec 19, 2016 PST 19.12.2016 09:55:50.000 -08:00
12:55:50 Dec 19, 2016 EST 19.12.2016 12:55:50.000 -05:00


1 row selected.

Edit: Moreover, as Wernfried shows in his answer, it is better to use Canada/Pacific rather than PST and Canada/Eastern rather than EST in the input string.

  • Interesting... TZR gives an ORA -01882 timezone region %s not found, but is does work in PLSQL. That may be enough. – Ray DeBruyn Dec 19 '16 at 23:18
  • btw the data comes from paypal and I have no control over it. In society, a date/time in this format is well understood. However, I may have locked myself in a sandbox just thinking north american. Perhaps I need to peer over the edge lol The little documentation I've found does seem to support the TZD format as opposed to TZR TZD. However, there is not much on the format masks. I'll see if I can prove this out in the coming days. Thanks for the quick response :) – Ray DeBruyn Dec 19 '16 at 23:26