چگونگی تبدیل تاریخ UTC به Local در SQL

در بسیاری از دیتابیس ها که باهاش سر و کار داریم تاریخ ها به صورت UTC ذخیره شده. UTC یا Coordinated Universal Time یک معیار زمان برای تعیین ساعت‌ها و اختلافات زمانی در نقاط مختلف زمینه (با گرینویچ فرق داره!). هر منطقه زمانی از کره زمین با توجه به اختلاف مثبت یا منفی‌ با UTC تعیین میشه که این مقدار با TZ نشون داده میشه. به طور مثال در ایران با توجه به ساعت تابستانی یا همون daylight saving time؛ که در زمان نوشتن این مقاله تصمیم به حذفش گرفته شده از سال 1402؛ در نیمه اول سال به خاطر یک ساعت به جلو کشیده شدن ساعت ها در ابتدای بهار 4.5 ساعت و در نیمه دوم سال 3.5 ساعت با UTC اختلاف وجود داره. البته با علامت مثبت یعنی 3.5 ساعت از زمان UTC ساعت ایران جلوتره!

چطور تاریخ UTC رو به Local تبدیل کنیم؟ اینکار با استفاده از توابع کدنویسی شده یا همون CLR امکان پذیره ولی روش دیتابیسی (مد نظر sql server) یا کوئری نویسی شده چیه؟

پاسخ: با نوشتن عبارت زیر و جایگزینی ستون تاریخ مورد نظر با [Column_Name] این امکان تبدیل به وجود میاد.

CONVERT(
  datetime, 
  SWITCHOFFSET(
    [Column_Name], 
    DATEPART(
      TZOFFSET, [Column_Name] AT TIME ZONE 'Iran Standard Time'
    )
  )
)

و اما توضیحات :

در مجموع تبدیل زمان طی این مراحل انجام شد:

1. اضافه کردن TZ به تاریخ UTC با استفاده از AT TIME ZONE

2. استخراج TZ از تاریخ با استفاده از DATEPART

3. تبدیل زمان UTC به زمان Local با استفاده از SWITCHOFFSET و TZ استخراج شده

قدم اول: برای بدست آوردن قسمت TZOFFSET، با استفاده از AT TIME ZONE به تاریخ TZOFFSET رو اضافه می کنیم. در مثال زیر به تاریخ UTC با توجه به تاریخ سیستم یا سرور مقدار TZOFFSET اضافه میشه که در این جا با توجه به تاریخ که در نیمه اول سال هست 04:30+ خواهد بود.

قدم دوم: تابع DATEPART که مقدار عدد صحیح بخشی از تاریخ ورودی به تابع رو بر می گردونه به طور مثال:

با استفاده از TZ یا TZOFFSET امکان استخراج مقدار TIME ZONE رو از تاریخ با استفاده از این تابع داریم. در صورتی که تاریخ در نیمه اول سال باشه 270 و در صورتی که در نیمه دوم سال باشه 210 برگردونده میشه. (البته الان با ثابت شدن که با Daylight saving time شناخته میشه این اختلاف در نیمه اول و دوم سال وجود نداره)

قدم سوم: تبدیل این زمان و تاریخ به TZOFFSET مورد نظر هست که تابع SWITCHOFFSET به ما در این کار کمک می کنه.

اگر همه این تکه ها رو به هم متصل کنیم خواهیم داشت:

SELECT 
  GETDATE() AS LocalDateTime, 
  GETUTCDATE() AS UTCDateTime, 
  CONVERT(
    datetime, 
    SWITCHOFFSET(
      GETUTCDATE(), 
      DATEPART(
        TZOFFSET, 
        GETUTCDATE() AT TIME ZONE 'Iran Standard Time'
      )
    )
  ) AS Converted

البته برای تبدیل فرمت میتونیم با استفاده از Convert و datetime تاریخ رو به فرمت موردنظرمون تبدیل می کنیم.

و با استفاده از تابع FORMAT تاریخ رو به شمسی هم تبدیل کنیم:

FORMAT(
  CONVERT(
    datetime, 
    SWITCHOFFSET(
      GETUTCDATE(), 
      DATEPART(
        TZOFFSET, 
        GETUTCDATE() AT TIME ZONE 'Iran Standard Time'
      )
    )
  ), 
  'yyyy-MM-dd HH:mm', 
  'fa-ir'
)