Function that returns offset based on timezone

db2user24 · #1 (**permalink**) 08-16-10, 11:26

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

ARWinner · #2 (**permalink**) 08-16-10, 11:46

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

db2user24 · #3 (**permalink**) 08-16-10, 16:25

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..

ARWinner · #4 (**permalink**) 08-16-10, 16:31

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

Internet Services

Web Development

Digital Marketing

Consumer Tech