Dates

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

Dates

The Viewpoint Framework allows the user be able to configure their portal to view data in any timezone they desire and store this preference on the server. Database data on the Viewpoint Server is available as UTC, which is to be converted to user timezone on the display side (browser).

The Problems

Because Daylight Savings Time occurs at different times for different timezones, this requirement presents difficulty when confronted with the limitations of the Javascript Date Object. The Javascript Date Object can deal with dates in UTC (GMT) or in the Browser's local timezone. As a client, the browser has access to the timezone rules in effect on the host operating system. The timezone table of the host system is not available to Javascript, so the Date object has no insight to information about other timezones.

Teradata Viewpoint includes the GoogleCode/Coolite javascript date library. Online documentation is available for this library. One interesting feature of this date library is that it extends the Date Object with a setTimezoneOffset method. The setTimezoneOffset method works around some of the difficulties by changing the Date Object's UTC by the corresponding offset. This precludes the fetching of true UTC from the Date object. More importantly, this imposes the browser's current timezone rules over the faked timezone, which is wrong.

Different locales have different rules for changing to and from Daylight Savings Time. In the United States, participating states must change at 2AM local time, but not all states participate. In Europe, most countries change at the same UTC time even though they are in 3 different timezones. Another example is that San Diego and Tijuana have the same timezone offset from UTC, but change their clocks at different calendar dates.

The Solution

The Java Date Object has knowledge about most timezone and Daylight Savings Time change rules. By obtaining the date of the previous and next timezone changes, the client can determine the timezone offset of the date that it is working with and display it appropriately. The existing getUserTimeZone tag was extended to provide the necessary additional information via the timeZoneChanges variable.

taglib prefix=uri="http://teradata.com/viewpoint/core"
<vp:getUserTimeZone />
<script>setTimezoneOffset(${timeZoneChanges});</script>


A small obstacle was found in that the Java Date Object does not provide the actual transition dates for Daylight Savings Time. To solve this problem, a custom algorithm was developed to find transition dates on the server. Although it is not costly to compute the transition dates, these dates are in effect for a long time and server can cache these dates by timezone for up to a week without any problem.

Determining Daylight Savings Time Change Dates

Start by checking if the timezone offset in 125 days is the same as now. If the same, then the transition may be further out, so add 62.5 days, otherwise subtract 62.5 days. Keep halving the interval until within .001 days (+/- 86.4 seconds). At this point, round to the nearest 15 minutes. If we reach 245 days without a transition, then there is no DST transition date. This Algorithm takes about 17 iterations per transition.

Information provided by the Server

The getUserTimeZone tag emits an extension to the Javascript Date Object: tzData. It is a javascript array containing 3 previous and 1 future time/timestamp pairs. Timezones that do not use Daylight Savings Time will return 1 for stamp. This sample tz data has offsets of 300 and 360 minutes.

Date.prototype.tzData = [ // Central
{ stamp:1225609200000, tz:360 }, // Nov 2, 2008 (future)
{ stamp:1205049600000, tz:300 }, // March 9, 2008 (current)
{ stamp:1194159600000, tz:360 }, // Nov 4, 2007 (prev)
{ stamp:1173600000000, tz:300 } // March 11, 2007 (year ago)
];


This sample tz data has no Daylight Savings Time changes.

Date.prototype.tzData = [ // India
{ stamp:1, tz:-330 } // No Daylight Savings Time
];


Why not use the Server for All Date Conversions?

The presentation side has to perform calculations on dates other than those contained within the data set. While we could go back to the server for each date we wish to show on a chart axis (every 2nd day, for example), doing so would not be an efficient solution to the problem. Performing computational tasks on dates or sending all date strings that might need to be displayed from the server to the client are not really feasible solutions. The presentation engine might wish to zoom in on data and should be able to do so without going back to the server.

Date Object Extensions

These functions are analogous to the corresponding Javascript get/set commands except that they act with the knowledge of the user's configured timezone. This means that they have knowledge of the necessary corrections when adding or subtracting time.

setLocalFullYear(y[,m,d])

Sets the local year (and optionally month and day of the month).

setLocalMonth(m[,d])

Sets the local month (0-11, and optionally day of the month).

setLocalDate(d)

Sets the local day of the month.

setLocalHours(h[,m,s,ms])

Sets the local hour field (and optionally minutes, seconds, and milliseconds fields)

setLocalMinutes(m[,s,ms])

Sets the local minutes field (and optionally seconds and milliseconds fields)

setLocalSeconds(s[,ms])

Sets the local seconds field (and optionally the milliseconds field)

setLocalMilliseconds(ms)

Sets the local millisecond field

getLocalFullYear()

Returns the local full year.

getLocalMonth()

Returns the local month (0-11)

getLocalDate()

Returns the local day of the month.

getLocalDay()

Returns the local day of the week.

getLocalHours()

Returns the local hour field.

getLocalMinutes()

Returns the local minutes field.

getLocalSeconds()

Returns the local seconds field.

getLocalMilliseconds()

Returns the local milliseconds field.

format(type)

The Date.format extension provides a standardized way to display a large number of date formats. You specify which fields you wish to display and format() will display them in the appropriate order and with the correct separators.

"Ymdhms" full year, month, day, hours, minutes, seconds

"Ymdhm" full year, month, day, hours, minutes

"Ymdh" full year, month, day, hours

"Ymd" full year, month, day

"ymdhms" year, month, day, hours, minutes, seconds

"ymdhm" year, month, day, hours, minutes

"ymdh" year, month, day, hours

"ymd" year, month, day

"mdhms" month, day, hours, minutes, seconds

"mdhm" month, day, hours, minutes

"mdh" month, day, hours

"md" month, day

"hms" hours, minutes, seconds

"hm" hours, minutes

"h" hours, am/pm

"Month" long month, eg. March

"month" short month, eg. Mar

"Day" long day of the week, eg. Sunday

"day" short day of the week, eg. Sun

"Date" full year, month, day

"date" year, month, day

"time" hours, minutes, seconds

format() supports custom formatters as per datejs.

For example, "MMMM d tt" could return "March 9 PM"

Internationalization and Localization

The format used for dates varies by country. For example, for FR (France), dates are printed as dd/MM/yyyy while for US, dates are printed as MM/dd/yyyy Currently, the Date.format() extension determines the date format the user will see based the language that is reported by the browser. However, this may one day become a user configurable timestamp.

Tags (2)
1 REPLY

Re: Dates

Something to say? Add your comment here.