[MSSQL] 세계 시간(타임존, Time zone) 계산 함수(function)

개발배경(요구사항)

웹사이트의 로그인 일시를 사용자의 타임존에 맞게 변경해서 보여줘야한다. 

DB에 저장되는 [로그인일시]는 한국시간(Asea/Seoul, GMT +09:00) 이다.

예)싱가폴 법인 사용자가 싱가폴시간 2018-12-16 15:00 에 로그인하고 나중에 타임존을 미국캘리보니아로 변경하면 접속일시를 모두 미국시간으로 보여준다.

즉.

DB에 저장된 로그인 시간은

한국시간(Asea/Seoul, GMT +09:00)  2018-12-16  16:00

싱가포르                2018-12-16 15:00(1시간차)

미국 캘리포니아       2018-12-15 23:00 (16시간차)

 

=>db설계시 로그인기록에 한국시간, 타임존,표준시간 저장.

컬럼명                                          값

-----------------------------         --------------

userid                                     tester

connectTimeKr(한국시간)            2018-12-16 16:00

timezone(GMT)                        싱가폴

connectTimeGmt(표준시)            2018-12-16 12:00

 

=> 조회 시 

DB에 저장된 표준시를 로그인한 사용자의 타임존으로 변환해서보여줌.

 

 

함수

/* *************************************************************** *    

   작성자 :     

   작성일 :     

   설  명 : 입력 된 시간을 표준시로 변경하고 표준시를 타임존에 해당하는 GMT시간으로 변경

   실  행 : select dbo.UF_TIMEZONE(-8,'2019-03-27 15:30:13.563');             

   

   select SYSDATETIME() AS '한국시간',

                   DATEADD(HOUR, -9, SYSDATETIME()) AS '표준시',

                DATEADD(HOUR, -8, DATEADD(HOUR, -9, SYSDATETIME())) AS '태평양 표준시'    --(GMT-08: 00) 태평양 표준시 (미국 및 캐나다); 티후아나

 

* *************************************************************** */    

CREATE Function [dbo].[UF_TIMEZONE]    

(    

 @i_offset INT,          /*입력된 시간의 GMT값 -표준시로 변환하기 위한 값*/

 @i_gmt  INT,          /*변환할 타임존 값*/ 

 @i_date  DATETIME      /*시간*/

)     

RETURNS DATETIME

    

AS      

BEGIN    

  DECLARE @Rtn DATETIME;  /*타임존 계산된 결과값*/  

  

  /* 타임존 시간 계산

    1. 입력된 시간을 표준시로 변경한다.표준시= 입력시간-(OFFSET)

       DATEADD(HOUR, -(@i_offset), @i_date)

    2. 타임존 계산. 표준시 + GMT시간

       DATEADD(HOUR, @i_gmt, DATEADD(HOUR, -(@i_offset), @i_date))

  */

  

  SELECT @Rtn = DATEADD(HOUR, @i_gmt, DATEADD(HOUR, -(@i_offset), @i_date)) ;    

    

  RETURN @Rtn;    

END    

 

 

 

#샘플

한국시간 : 2019-03-29 13:00:00.000

=> 싱가포르 시간 으로 변환

 

/****************************

   한국(서울)의 GMT값은 +9.

   싱가포르의 GMT값은 +8.    

 ****************************/

SELECT dbo.UF_TIMEZONE(+9, +8,  '2019-03-29 13:00:00.000');

---------

'2019-03-29 12:00:00.000'

 

#사용 예제

  사용자의 타임존이 싱가포르(GMT값 +8)인 경우.

 

   - 리스트 및 상세화면에서는 두 번째 인자값만 사용자의 타임존(GMT)값으로 변경해주면 됨

   SELECT no
         ,dbo.UF_TIMEZONE(+9, +8, date)   /*DB에 저장된 승인일시는 한국시간임. */
     FROM Table

 

 

    - 리스트에서 일자 조건을 '2019-03-27'~'2019-03-28'로 검색 시

 

SELECT no
       , dbo.UF_TIMEZONE(+9, +8, date)  /*DB에 저장된 일시는 한국시간임*/
  FROM Table
 WHERE date >= dbo.UF_TIMEZONE(+8, +9, '2019-03-27 00:00:00.000') /*입력된 시간을 한국시간으로 변경. 컬럼에 함수를 사용하면 인덱스 사용불가.*/
   AND date < dbo.UF_TIMEZONE(+8, +9, '2019-03-28 23:59:59.000') ​

 

 

# 검증 방법

아래 사이트에서 검증하세요

https://www.timeanddate.com/worldclock/converter.html?iso=20190221T120000&p1=235&p2=236

 

 

 

# 참고사이트

https://docs.microsoft.com/ko-kr/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-2017

 

http://hansworld.co.kr/Instant_Backup/314

 

https://www.timeanddate.com/worldclock/converter.html?iso=20190221T120000&p1=235&p2=236