So there’s this Oracle database instance in the midwest, in US\/Central time zone. <\/p>\n
So if I use PuTTY to open a terminal window on the host and open a SQL*Plus session, if I execute this statement I’ll get the following results:<\/p>\n
SQL<\/span>> select<\/span>
max<\/span>( CHANGE_TS ) as<\/span> MAX_CHANGE,
max<\/span>( CHANGE_TS at<\/span> time<\/span> zone<\/span> 'US\/Central'<\/span> ) as<\/span> MAX_CST_CHANGE
from<\/span> tRetired_Mst;
MAX_CHANGE MAX_CST_CHANGE
-------------------------------- ---------------------------------------------<\/span>
05-JAN-17 02.33.17.613000 PM 05-JAN-17 02.33.17.613000 PM US\/CENTRAL
<\/pre>\n<\/div>\n
As expected, they are the same, because the local time zone of the session (terminal window) is the same as the DB, US\/Central<\/strong>.<\/p>\nHowever: <\/p>\n
If I run PL\/SQL Developer on my Windows Laptop here in California, and connect to the same Oracle instance, then the results are different:<\/p>\n
\nSQL<\/span>> select<\/span>
max<\/span>( CHANGE_TS ) as<\/span> MAX_CHANGE,
max<\/span>( CHANGE_TS at<\/span> time<\/span> zone<\/span> 'US\/Central'<\/span> ) as<\/span> MAX_CST_CHANGE
from<\/span> tRetired_Mst;
MAX_CHANGE MAX_CST_CHANGE
----------------------------- ----------------------------------------------<\/span>
2017-01-05 14:33:17 05-JAN-17 04.33.17.613000 PM US\/CENTRAL<\/pre>\n<\/div>\n
Observe thatthe MAX_CST_CHANGE value is off by two hours! Because the timestamp value was evaluated as if it were in the local session time of my laptop, which is US\/Pacific<\/strong>.<\/p>\nFor code executing in PL\/SQL packages in the database, this is probably not an issue… but if you are using this technique to convert values in ad-hoc SQL, you should be aware of this trap.<\/p>\n","protected":false},"excerpt":{"rendered":"
So there’s this Oracle database instance in the midwest, in US\/Central time zone. So if I use PuTTY to open a terminal window on the host and open a SQL*Plus session, if I execute this statement I’ll get the following results: SQL> select max( CHANGE_TS ) as MAX_CHANGE, max( CHANGE_TS at time zone ‘US\/Central’ ) […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-28","post","type-post","status-publish","format-standard","hentry","category-oracle","post-preview"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/28","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/comments?post=28"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/28\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/media?parent=28"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/categories?post=28"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/tags?post=28"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}