Solved

Converting Historical DateTime from GMT-PST Including DST.

Posted on 2007-11-28
3
1,497 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:Kent Olsen
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:
Kent Olsen 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
countTriple  challenge 8 84
hasOne  challenge 59 145
object oriented javascript web form 8 144
Odds of picking games correctly 4 131
The CRUD Functions CRUD, meaning "Create, Read, Update, Delete (http://en.wikipedia.org/wiki/Create,_read,_update_and_delete)" is a common term to data base developers.  It describes the essential functions of data base table maintenance.  This art…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

792 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