Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Function that returns offset based on timezone

    Hi, do any of you know if there's any way to create a SQL based function in DB2 that takes 'Month', 'Day', 'Year' and 'Timezone' as parameters and returns the time with the correct gmt offset?

    I don't think that DB2 is very timezone oriented so not sure if I can achieve this.. for example, let's call the function GET_OFFSET.. it will have 4 parameters

    GET_OFFSET ( month INTEGER, day INTEGER, year INTEGER, timezone VARCHAR(64))

    --> example : month = 7, day = 1, year =2010, timezone = 'US/Central'
    --> result returned = 2010-07-01-05.00.00.000000 since the GMT offset is 5 hours

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your function needs to be able to do 3 things:

    1) convert the Month, Day, and Year to a DATE value
    2) Convert the timezone parameter to an offset
    3) determine if the Date derived in (1) is DST or not

    Then you would return something like the following: DATE - offset - DST hours

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thanks, 1 is not a problem at all... 2 & 3 not so sure about them.. especially 3, how do I know if it is DST or not..just don't know if there is an easy way to do this..

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Here is a UDF that I made that determines if a data is DST. This is based only on US Law. If DST is determined differently outside the US, then this will not work.

    Andy

    Code:
    CREATE FUNCTION MY_UDFS.UDF_IS_DATE_DST(ADATE DATE)
    RETURNS SMALLINT LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION
    RETURN
    CASE WHEN YEAR(UDF_IS_DATE_DST.ADATE) >= 2007 THEN
    
    CASE MONTH(UDF_IS_DATE_DST.ADATE)
    WHEN 1 THEN 0
    WHEN 2 THEN 0
    WHEN 3 THEN CASE WHEN DAY(UDF_IS_DATE_DST.ADATE) > 14 THEN 1
                     WHEN DAY(UDF_IS_DATE_DST.ADATE) < 8 THEN 0
                     ELSE CASE WHEN DAYOFWEEK(UDF_IS_DATE_DST.ADATE) = 1 THEN 0
                               ELSE CASE WHEN (DAY(UDF_IS_DATE_DST.ADATE) - DAYOFWEEK(UDF_IS_DATE_DST.ADATE)) < 7 THEN 0
                                          ELSE 1
                                          END
                               END
                     END
    WHEN 4 THEN 1
    WHEN 5 THEN 1
    WHEN 6 THEN 1
    WHEN 7 THEN 1
    WHEN 8 THEN 1
    WHEN 9 THEN 1
    WHEN 10 THEN 1
    WHEN 11 THEN CASE WHEN DAY(UDF_IS_DATE_DST.ADATE) > 7 THEN 0
                      ELSE CASE WHEN DAYOFWEEK(UDF_IS_DATE_DST.ADATE) = 1 THEN 1
                                ELSE CASE WHEN (DAY(UDF_IS_DATE_DST.ADATE) - DAYOFWEEK(UDF_IS_DATE_DST.ADATE)) < 0 THEN 1
                                          ELSE 0
                                          END
                                END
                      END
    WHEN 12 THEN 0
    END
    
    ELSE
    
    CASE MONTH(UDF_IS_DATE_DST.ADATE)
    WHEN 1 THEN 0
    WHEN 2 THEN 0
    WHEN 3 THEN 0
    WHEN 4 THEN CASE WHEN DAY(UDF_IS_DATE_DST.ADATE) > 7 THEN 1
                     ELSE CASE WHEN DAYOFWEEK(UDF_IS_DATE_DST.ADATE) = 1 THEN 0
                               ELSE CASE WHEN (DAY(UDF_IS_DATE_DST.ADATE) - DAYOFWEEK(UDF_IS_DATE_DST.ADATE)) < 0 THEN 0
                                          ELSE 1
                                          END
                               END
                     END
    WHEN 5 THEN 1
    WHEN 6 THEN 1
    WHEN 7 THEN 1
    WHEN 8 THEN 1
    WHEN 9 THEN 1
    WHEN 10 THEN CASE WHEN DAY(UDF_IS_DATE_DST.ADATE) < 25 THEN 1
                      ELSE CASE WHEN DAYOFWEEK(UDF_IS_DATE_DST.ADATE) = 1 THEN 1
                                ELSE CASE WHEN (DAY(UDF_IS_DATE_DST.ADATE) - DAYOFWEEK(UDF_IS_DATE_DST.ADATE)) < 24 THEN 1
                                          ELSE 0
                                          END
                                END
                      END
    WHEN 11 THEN 0
    WHEN 12 THEN 0
    END
    
    END

Posting Permissions

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