Solved

Converting Historical DateTime from GMT-PST Including DST.

Posted on 2007-11-28
3
1,459 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
Comment Utility
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
Comment Utility

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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The greatest common divisor (gcd) of two positive integers is their largest common divisor. Let's consider two numbers 12 and 20. The divisors of 12 are 1, 2, 3, 4, 6, 12 The divisors of 20 are 1, 2, 4, 5, 10 20 The highest number among the c…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

21 Experts available now in Live!

Get 1:1 Help Now