At ABACAB we have – very correctly – a TIMESTAMP WITH TIME ZONE column called ONLINE_ENROLLMENT_TIME that has been populated – via an ETL process – with values in UTC.

This is good, because it is a completely unambiguous value. Oh, if only all our date times were stored this way.

Unfortunately, the convention up until now has been:

  • assume date times are in Central time zone
  • convert to integer “date key” and “time key” data types (primary key for date and time dimensions).

This loses any time zone information, which is not great. It means that all client processes using this data also need to assume Central time zone.

The following SQL takes our time zone-aware column and transforms it:

select
   ID,
   ONLINE_ENROLLMENT_TIME,
   (ONLINE_ENROLLMENT_TIME at time zone 'US/Central')                         as ONLINE_ENROLLMENT_TIME_C,
   trunc( cast( (ONLINE_ENROLLMENT_TIME at time zone 'US/Central') as DATE )) as ONLINE_ENROLLMENT_DATE
from ZYXX.D_Person
where ONLINE_ENROLLMENT_TIME
is not null;

Results:

image