对甲骨文来说最好的方法:

硬编码的datetime:

SELECT TO_CHAR(CAST((FROM_TZ(CAST(TO_DATE('2018-10-27 21:00', 'YYYY-MM-DD HH24:MI') AS TIMESTAMP), 'UTC') AT  TIME ZONE 'EET') AS DATE), 'YYYY-MM-DD HH24:MI') UTC_TO_EET FROM DUAL

结果: 2018-10-28 00:00

列名和表名:

SELECT TO_CHAR(CAST((FROM_TZ(CAST(COLUMN_NAME AS TIMESTAMP), 'UTC') AT  TIME ZONE 'EET') AS DATE), 'YYYY-MM-DD HH24:MI') UTC_TO_EET FROM TABLE_NAME

最简单的答案并不总是在底部，但这一次是，并且可以在上面的评论中看到。 使用您自己的“AT TIME ZONE”来捕获列/数据字段的TzOffset，而不是当前的SYSDATETIME。 在下面的数据中，2个查询，一个关于feb数据(DST是关闭的，在阿姆斯特丹的冬天)+1差异 第二次查询阿姆斯特丹4月份的数据，所以+2小时的差异。

    select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring
   ,  receiveTimeUTC
   ,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTimeWrongNoDST
   ,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, receiveTimeUTC  AT TIME ZONE 'Central European Standard Time' ))) as LocalTimeWithDST
       from sensordetails order by id

    select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring, receiveTimeUTC
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTimeWrongNoDST
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, receiveTimeUTC  AT TIME ZONE 'Central European Standard Time' ))) as LocalTimeWithDST
       from sensordetails order by id desc

结果:

所以这是一个T-SQL (SQL Server Answer)，不需要函数的storedproc。

这很简单。试试这个Azure SQL Server:

SELECT YourDateTimeColumn AT TIME ZONE 'Eastern Standard Time' FROM YourTable

对于本地SQL Server:

SELECT CONVERT(datetime2, SWITCHOFFSET(CONVERT(datetimeoffset, gETDATE()), DATENAME(TzOffset, gETDATE() AT TIME ZONE 'Eastern Standard Time'))) FROM YourTable

UNIX时间戳只是一个特定日期和UNIX纪元之间的秒数，

SELECT DATEDIFF(SECOND，{d '1970-01-01'}，GETDATE()) //返回SQL server中的UNIX时间戳

你可以创建一个函数，使用Country Offset将本地日期时间转换为Unix UTC SQL server中的Unix时间戳函数

下面是一个将夏令时考虑在内的更简单的例子

CREATE FUNCTION [dbo].[UtcToLocal] 
(
    @p_utcDatetime DATETIME 
)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE())
END

对于任何仍然试图解决这个问题的人，这里有一个在SQL Server 2017中工作的概念证明

 declare
    @StartDate date = '2020-01-01'

;with cte_utc as
(
    select 
         1 as i
        ,CONVERT(datetime, @StartDate) AS UTC
        ,datepart(weekday, CONVERT(datetime, @StartDate)) as Weekday
        ,datepart(month, CONVERT(datetime, @StartDate)) as [Month]
        ,datepart(YEAR, CONVERT(datetime, @StartDate)) as [Year]
        
    union all

    Select
         i + 1
        ,dateadd(d, 1, utc)
        ,datepart(weekday, CONVERT(datetime, dateadd(d, 1, utc))) as Weekday
        ,datepart(month, CONVERT(datetime, dateadd(d, 1, utc))) as [Month]
        ,datepart(YEAR, CONVERT(datetime, dateadd(d, 1, utc))) as [Year]
    from    
        cte_utc
    where
        (i + 1) < 32767

), cte_utc_dates as 
(
    select 
        *,
        DENSE_RANK()OVER(PARTITION BY [Year], [Month], [Weekday] ORDER BY Utc) WeekDayIndex
    from
        cte_utc

), cte_hours as (
    select 0 as [Hour]
    union all
    select [Hour] + 1 from cte_hours where [Hour] < 23
)

select
    d.*
    , DATEADD(hour, h.Hour, d.UTC) AS UtcTime
    ,CONVERT(datetime, DATEADD(hour, h.Hour, d.UTC) AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time') CST
    ,CONVERT(datetime, DATEADD(hour, h.Hour, d.UTC) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') EST
from
    cte_utc_dates d, cte_hours h
where
    ([Month] = 3 and [Weekday] = 1 and WeekDayIndex = 2 )-- dst start
    or 
    ([Month] = 11 and [Weekday] = 1 and WeekDayIndex = 1 )-- dst end
order by
    utc
OPTION (MAXRECURSION 32767)

GO