Skip to content

Instantly share code, notes, and snippets.

@afruzan
Last active March 18, 2021 12:24
Show Gist options
  • Save afruzan/062d58b1b12d4366bcb273338ea37631 to your computer and use it in GitHub Desktop.
Save afruzan/062d58b1b12d4366bcb273338ea37631 to your computer and use it in GitHub Desktop.
Iran Official Date And Time (Jalali calendar - Tehran TimeZone) Solution in SQL raw and linq query (ef core 5).
declare @myutcdate as datetime2 = CAST('2021-03-17 22:00' AS datetime2);
select
FORMAT(SWITCHOFFSET(@myutcdate, '+03:30'), 'yyyy-MM-dd', 'fa') as 'get iran date',
FORMAT(SWITCHOFFSET(@myutcdate, '+03:30'), 'yyyy-MM', 'fa') as 'get iran year-month',
CAST(FORMAT(SWITCHOFFSET(@myutcdate, '+03:30'), 'dd', 'fa') as int) as 'get iran day',
CAST(FORMAT(@myutcdate, 'dd', 'fa') as int) as 'invalid time-zone results in invalid date.'
@afruzan
Copy link
Author

afruzan commented Mar 18, 2021

test performance on about 4m records

--select RecievedDateTime from Table -- 00:11

--select Format(RecievedDateTime , 'yyyy-MM-dd') from Table -- 00:44

--select dbo.GregorianToJalali(RecievedDateTime , 'yyyy-MM-dd') from Table -- 03:42

--select Format(RecievedDateTime , 'yyyy-MM-dd', 'fa') from Table -- 03:47

--select dbo.GregorianToJalali(RecievedDateTime , 'MM') from Table -- 03:35

--select Format(RecievedDateTime , 'MM', 'fa') from Table -- 01:54

--select Format(RecievedDateTime , 'yyyy-MM', 'fa') from Table -- 02:33

--select Format(SWITCHOFFSET(RecievedDateTime, '+03:30'), 'yyyy-MM', 'fa') from Table -- 02:33

--select Left(RecievedDateTime_Iran_StoredComputedColumn, '7') from Table -- 00:12 -- getting yyyy-MM

@afruzan
Copy link
Author

afruzan commented Mar 18, 2021

1. Add custom/built-in functions in a static class

    public static class CustomFunctions
    {
        public static DateTime ChangeTimeZoneOffset(this DateTime datetime, string timeZoneOffset)
        {
            throw new NotSupportedException();
        }
        public static DateTime ChangeTimeZoneOffset(this DateTime datetime, int timeZoneOffset)
        {
            throw new NotSupportedException();
        }
        public static string Format(this DateTime datetime, string format)
        {
            throw new NotSupportedException();
        }
        public static string Format(this DateTime datetime, string format, string culture)
        {
            throw new NotSupportedException();
        }
    }

2. Map those functions correctly:

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.ChangeTimeZoneOffset), new[] { typeof(DateTime), typeof(string) }))
                .HasName("SwitchOffset").IsBuiltIn();

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.ChangeTimeZoneOffset), new[] { typeof(DateTime), typeof(int) }))
                .HasName("SwitchOffset").IsBuiltIn();

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.Format), new[] { typeof(DateTime), typeof(string) }))
                .HasName("Format").IsBuiltIn();

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.Format), new[] { typeof(DateTime), typeof(string), typeof(string) }))
                .HasName("Format").IsBuiltIn();

3. Use it in your LINQ:

    ...
    .Select(i => new
    {
        TheDateTimeInUserTimeZone = CustomFunctions.ChangeTimeZoneOffset(i.MyDateTime, timeZoneOffsetVariable)
    }

If your time zone offset is static (eq your user country is static but datetimes are stored in UTC), One solution is using Computed Column:

entity.Property(e => e.TheDateTimeInIRIran).HasComputedColumnSql("(SWITCHOFFSET([MyDateTime], '+03:30'))", false);

also you can convert date from greg to jalali for example:

entity.Property(e => e.TheDateTimeInIRIran).HasComputedColumnSql("(FORMAT(SWITCHOFFSET([MyDateTime], '+03:30'), 'yyyy/MM/dd HH:mm:ss', 'fa-ir'))", false);

or use At Time Zone clause (SQL Server 2016+) supporting daylight saving time.

@afruzan
Copy link
Author

afruzan commented Mar 18, 2021

test performance on about 4m records

--select RecievedDateTime from Table -- 00:11

--select Format(RecievedDateTime , 'yyyy-MM-dd') from Table -- 00:44

--select dbo.GregorianToJalali(RecievedDateTime , 'yyyy-MM-dd') from Table -- 03:42

--select Format(RecievedDateTime , 'yyyy-MM-dd', 'fa') from Table -- 03:47

--select dbo.GregorianToJalali(RecievedDateTime , 'MM') from Table -- 03:35

--select Format(RecievedDateTime , 'MM', 'fa') from Table -- 01:54

--select Format(RecievedDateTime , 'yyyy-MM', 'fa') from Table -- 02:33

--select Format(SWITCHOFFSET(RecievedDateTime, '+03:30'), 'yyyy-MM', 'fa') from Table -- 02:33

--select Left(RecievedDateTime_Iran_StoredComputedColumn, '7') from Table -- 00:12 -- getting yyyy-MM

Format function is not deterministic and cannot be used in StoredComputedColumns. so for last select we should use GregorianToJalali or another way.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment