{"id":32,"date":"2016-10-11T03:29:00","date_gmt":"2016-10-11T03:29:00","guid":{"rendered":"\/colin\/morethanfour\/post\/Dates-and-Time-Zone-conversion"},"modified":"2016-10-11T03:29:00","modified_gmt":"2016-10-11T03:29:00","slug":"dates-and-time-zone-conversion","status":"publish","type":"post","link":"https:\/\/spacefold.com\/colin\/morethanfour\/2016\/10\/11\/dates-and-time-zone-conversion\/","title":{"rendered":"Dates and Time Zone conversion"},"content":{"rendered":"

It’s time to take another look at some date\/time conversion techniques…<\/p>\n

Consider reading a DATE value from some source system, in a known time zone, and needing to convert it into some other time zone before saving into the destination table:<\/p>\n

\n
with<\/span>
src_date as<\/span> (
   select<\/span>
      -- ---------------------------------------------------------------------<\/span>
      -- Here's a date value from the source system. From the value itself<\/span>
      -- we can't tell anything significant about it:<\/span>
      -- ---------------------------------------------------------------------<\/span>
      to_date('2016-10-03 23:20'<\/span>,'yyyy-mm-dd hh24:mi'<\/span>)         as<\/span> INPUT_DT
   from<\/span> dual
),
assume_data as<\/span> (
   select<\/span>
      INPUT_DT,
      -- --------------------------------------------------------------------------<\/span>
      -- If we know the value is a US\/Pacific time zone value, we can declare that:<\/span>
      -- --------------------------------------------------------------------------<\/span>
      from_tz( cast<\/span>( INPUT_DT as<\/span> TIMESTAMP<\/span>) , 'US\/Pacific'<\/span> )   as<\/span> PACIFIC_TSTZ,
      -- --------------------------------------------------------------------------<\/span>
      -- Alternatively, what if we know it is in China Standard Time ?<\/span>
      -- --------------------------------------------------------------------------<\/span>
      from_tz( cast<\/span>( INPUT_DT as<\/span> TIMESTAMP<\/span>) , '+8:00'<\/span> )        as<\/span> CHINA_TSTZ
   from<\/span> src_date
)
select<\/span>
   -- ----------------------------------------------------------------<\/span>
   -- Now we have values that declare what time zone they belong to,<\/span>
   -- we can easily transform the value into datetimes for a different time zone:<\/span>
   -- ----------------------------------------------------------------<\/span>
   INPUT_DT,
   PACIFIC_TSTZ,
   CHINA_TSTZ,
   -- ----------------------------------------------------------------<\/span>
   -- convert the China Standard Time value into US\/Central:<\/span>
   -- ----------------------------------------------------------------<\/span>
   CHINA_TSTZ at<\/span> time<\/span> zone<\/span> 'US\/Central'<\/span>        as<\/span> CENTRAL_TSTZ ,
   -- ----------------------------------------------------------------<\/span>
   -- convert the US\/Pacific time into UTC:<\/span>
   -- ----------------------------------------------------------------<\/span>
   PACIFIC_TSTZ at<\/span> time<\/span> zone<\/span> 'UTC'<\/span>             as<\/span> UTC_TSTZ,
   PACIFIC_TSTZ at<\/span> time<\/span> zone<\/span> '+0:00'<\/span>           as<\/span> UTC_TSTZ_ALT
from<\/span> assume_data;<\/pre>\n
<\/div>\n
Results:<\/p>\n
\"image\"<\/p>\n
For example, when loading DATE values from C2R (Pacific time) and storing them in EDW (UTC) we could use the following expression:<\/p>\n
\n
cast<\/span>( from_tz(cast<\/span>( INPUT_DT as<\/span> TIMESTAMP<\/span>), 'US\/Pacific'<\/span>) at<\/span> time<\/span> zone<\/span> 'UTC'<\/span> as<\/span> DATE<\/span> )<\/pre>\n
<\/div>\n
That is, if we really needed a DATE data type. I believe Oracle DB would handle the outer cast automatically if we attempted to save the TIMESTAMP+TZ value into a destination table’s DATE column<\/p>\n
For more fun:<\/p>\n