Time Zone Shifting

The previous section showed you how to select a timezone that governs the TimelineView as a whole. However, it doesn't solve the problem of mixed Timezone data. Let's consider the following example:

I have a TLV which shows data measures for shipments across multiple sites. Each site may be in a different timezone. We want each shipment's shipped quantity to be aggregated into the bucket for its "business day". So if the Shipment arrives on Nov 2nd for its Timezone, we want it to be displayed in the Nov 2nd bucket, even if the precise "point in time" for that value falls on Nov 1st in a different timezone.

In this case, because the shipments come from many different sites, I can't choose just one timezone in which to show the Timeline View. Instead, I need a way to "shift" the values so that they appear in the correct bucket. To achieve this, we must make two changes.

First, add the timezone as an additional column to our data measure SQL (see "SITE.TIME_ZONE_ID" below):

<SqlDef Name="ShippedQuantity" GroupName="TLVTimezoneSQLs">
select ACTUAL_PICKUP_DATE, TOTAL_SHIPPED_QTY, SITE.TIME_ZONE_ID
from SHIPMENT_HEADER
join SITE on SITE.SYS_SITE_ID = SHIPMENT_HEADER.SHIPTO_SITE_ID
where ...


Then we instruct the DM definition to use this timezone information using the "SqlDateHandling" tag:

<DM bucketization="DAILY">
<Name>ShipmentsReceived</Name>
<DisplayName>Shipments Received</DisplayName>
<SqlDefName>ShippedQuantity</SqlDefName>
<SqlQuantityField levelType="Undefined">
<FieldName>TOTAL_SHIPPED_QTY</FieldName>
</SqlQuantityField>
<SqlDateField levelType="Undefined">
<FieldName>ACTUAL_PICKUP_DATE</FieldName>
</SqlDateField>
<SqlDateHandling timezoneSqlName="TIME_ZONE_ID">SHIFT_TO_TIMELINE_VIEW_TZ</SqlDateHandling>
</DM>


In the database, the SHIPMENT_HEADER.ACTUAL_PICKUP_DATE field is stored as an ordinary TIMESTAMP field. It does not contain timezone information. That means we know only the "point in time", i.e. millis since Epoch (00:00:00 UTC on 1 January 1970). In order to know what business day the value falls on, we must provide the timezone. This is done using the timezoneSqlName attribute of the SqlDateHandling tag. Using this, we know for example whether a TIMESTAMP falls on 12:00AM EST Nov 2 or 11:00PM CST Nov 1 (even though these are the same "point in time").

Once the timezone is identified, we then provide the keyword SHIFT_TO_TIMELINE_VIEW_TZ as the value of the SqlDateHandling tag. This will take all date values and shift them into the TimelineView's timezone before allocating to buckets. By shift, we mean we will shift the point in time such that the business day/hour/minute stays the same between the two timezones. For example:


  • Application server is in Pacific time.

  • User in Central time.

  • Actual Pickup Date point-in-time is 12:00AM EST Nov 2, and the Site's TZ is EST. (This means Oracle stores this date as 9:00 PM Nov 1, with PST as the implicit Timezone.)

  • Without SqlDateHandling, the value will be formatted in the user's TZ. Thus it will appear in the bucket for Nov 1, since 12:00AM EST Nov 2 formats as 11:00PM CST Nov 1

  • With SqlDateHandling, the value will appear in the bucket for Nov 2, as it will shift 12:00AM EST Nov 2 to Central Timezone, where it becomes 12:00AM CST Nov 2.