Solved

Converting Historical DateTime from GMT-PST Including DST.

Posted on 2007-11-28
3
1,487 Views
Last Modified: 2008-02-01
Client has 60+ years historical data that includes Date_Time stamp on records.  The Date_Time stamp was stored relative to GMT.  The GMT Date_Time stamp was recorded in the historical records which were, and remain, in the PST zone.

Client needs historical data converted.  The legacy Date_Time stamp of the historical data needs to NOW be stored as a Date_Time stamp in United States PST.

The Date_Time stamp must reflect (any?) impact that DST may have created since the imposition of the Uniform Time Act of 1966 (15 U.S. Code Section 260a).  Is there any impact? If so, would it be necessary to account for DST issues in BOTH GMT and PST regions (relative to the Date_Time stamp in a historical data record) since 1966?

I know it is a somewhat odd question and perhaps overly worded; however, inasmuch as technical and potential legal/accuracy/logistical issues may be involved, I would like additional viewpoints and/or facts.  Perhaps it is a simple or irrelevant question in the final analysis.

Depending on the response, I may post a separate follow up question to solicit a generic algorithm to code a solution.  But first things first.

Thanks so much for all of the forthcoming comments.

-Dan
0
Comment
Question by:jdg3
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 20373218
Hi Dan,

The ONLY valid reason to convert all of these dates from GMT to PT is that the legacy data is going to be loaded into tables that already contain PT dates.  If your client "just wants the dates changed" because the office (or server) is in the PT, you should try very hard to dissuade him.

GMT is a time series.  2AM always comes 3600 seconds after 1AM.  PST, PDT, EST, et al, are simply derivates of GMT.  One can even make a good argument that PST, PDT, etc. are simply DISPLAY derivates of GMT that are localized to a particular time zone.

By converting the timestamps to the local TZ the DB loses this chronology.  No longer is the difference between two time stamps the duration of an event (or the time between events).  Every date calculation must now be weighed against the local TZ.

If anything, consider converting the existing times to GMT and utilizing a date function to format it to local time when you want to display it.


Good Luck,
Kent
0
 

Author Comment

by:jdg3
ID: 20374909

Kent,

This is the kind of response for which I was hoping.  You are correct in the conversion observation.  This data is getting loaded in a DW.   I will have to review the issue of event chronology relevance.

So, since GMT is a series, DST in the GMT and the PST is really not relevant, correct?

Thanks,

Dan
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 20376250
Hi jdg3,

Well, "series" has specific implications within databases so that is probably a bad choice of words on my part.  But GMT data times are always incrementing.  If daylight saving time were not an issue it would be pretty easy to load all of the data as GMT - 8, or whatever is appropriate.

But DST adds twists that you want to handle at the display layer.  Since there can be multiple 1:30AM on the 'fall back' and an NO 1:30AM on the 'spring forward' day, a shift happens that can make the times awkward to use.  Joining on time when the time includes the 'fall back' date can lead to duplicate records.  Sorting on time is somewhat meaningless as there can now be overlaps.  Similarly, there is a 1 hour hole in the 'spring forward' day.  It looks for all the world that nothing happened during that hour.

That's why so may 24-hour systems are based on GMT+-bias.

I'd hate to design a data warehouse where time wasn't a constant.  (i.e. the stored date varied with DST)

Good Luck,
Kent
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Algorithms behind the java util package 5 121
Algorithm 5 136
How to determine sequence 8 92
factorial example challenge 10 103
Software development teams often use in-memory caches to improve performance. They want to speed up access to, or reduce load on, a backing store (database, file system, etc.) by keeping some or all of the data in memory.   You should implement a …
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now