Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Unanswered: Time Zone challenge - All ideas welcome

    Hi all,

    I have been tasked with an interesting challenge and was wondering if any of you experts out there might have any ideas on a solution. Basically, I have a table of packages:

    Code:
    CREATE TABLE [dbo].[tPackage] (
    	[PkgID] [int] NOT NULL PRIMARY KEY CLUSTERED ,
    	[PkgDelivZip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[PkgDelivDate] [smalldatetime] NULL ,
    	[HasBeenDeliv] [bit] NULL 
    ) ON [PRIMARY]
    GO
    
    INSERT INTO tPackage VALUES (1, '30022', '11/9/06 10:30 am', 1)
    INSERT INTO tPackage VALUES (2, '30022', '11/9/06 10:30 am', 0)
    INSERT INTO tPackage VALUES (3, '30022', '11/9/06 06:00 pm', 0)
    INSERT INTO tPackage VALUES (4, '96801', '11/9/06 10:30 am', 1)
    INSERT INTO tPackage VALUES (5, '96801', '11/9/06 10:30 am', 0)
    INSERT INTO tPackage VALUES (6, '96801', '11/9/06 06:00 pm', 0)
    And I need to retrieve a list of those packages which are late for delivery. Now if location was not a factor, I could just use the simple script:

    SELECT * FROM tPackage WHERE HasBeenDeliv = 0 AND PkgDelivDate < GetDate()

    Unfortunately, in the case of package 5, it is not after 10:30am in Hawaii, so this should not be considered late. I thought about having a table of zipcodes with their timezone differential, but the really big problem comes in when you consider the areas which do not observe Daylight Savings Time.

    Any thoughts?
    Thanks in advance for your feedback!
    Cat

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Daggone it! If people would just do the sensible thing and store all times in UCT these problems wouldn't happen to us poor DBAs! That's about as likely to happen as pigs learning to fly, but we can always hope...

    The folks at Great Data can help you. They mantain a list of zip codes, and one of the options you can purchase is time zone information. They use standard time zones, which have specific zone identifiers for areas that don't observe Daylight Savings Time.

    You're "on your own" for dealing with when the time changes from Standard to Daylight Savings time, I don't know of any way to cope with that, short of actually storing the times in the database as UCT and converting them for the UI.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Perhaps you should use UTC dates (which I believe do not play around with Daylight Savings schemes)? That way, you could use the getutcdate() function to determine if a package is late.

    EDIT Dang, I got sniped!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •