sql-server - query - sql server utc to local

TSQL: ¿Cómo convertir la hora local a UTC?(SQL Server 2008)

Question

Estamos lidiando con una aplicación que necesita manejar datos de tiempo globales de diferentes zonas horarias y configuraciones de horario de verano. La idea es almacenar todo en formato UTC internamente y solo convertir hacia adelante y hacia atrás para las interfaces de usuario localizadas. ¿El SQL Server ofrece algún mecanismo para tratar las traducciones dadas una hora, un país y una zona horaria?

Este debe ser un problema común, así que estoy sorprendido de que Google no muestre nada utilizable.

¿Alguna sugerencia?

Answer 1

Aquí el código para convertir una zona DateTime a otra zona DateTime

  DECLARE @UTCDateTime DATETIME = GETUTCDATE();
  DECLARE @ConvertedZoneDateTime DATETIME;

    -- ''UTC'' to ''India Standard Time'' DATETIME
    SET @ConvertedZoneDateTime = @UTCDateTime AT TIME ZONE ''UTC'' AT TIME ZONE ''India Standard Time''
    SELECT @UTCDateTime AS UTCDATE,@ConvertedZoneDateTime AS IndiaStandardTime

    -- ''India Standard Time'' to ''UTC'' DATETIME
    SET @UTCDateTime = @ConvertedZoneDateTime AT TIME ZONE ''India Standard Time'' AT TIME ZONE ''UTC''
    SELECT @ConvertedZoneDateTime AS IndiaStandardTime,@UTCDateTime AS UTCDATE

Nota : Esto ( AT TIME ZONE ) trabajando solo en SQL Server 2016+ y esta ventaja es considerar automáticamente Daylight mientras se convierte a una zona horaria particular

Answer 2

Puede usar la función GETUTCDATE () para obtener la fecha y hora UTC Probablemente puede seleccionar la diferencia entre GETUTCDATE () y GETDATE () y usar esta diferencia para ajustar sus fechas a UTC

Pero estoy de acuerdo con el mensaje anterior, que es mucho más fácil controlar el correcto datetime en la capa de negocios (en .NET, por ejemplo).

Answer 3

Puede usar mi proyecto de soporte de zona horaria de SQL Server para convertir zonas horarias estándar de IANA, como se detalla aquí .

UTC to Local es así:

SELECT Tzdb.UtcToLocal(''2015-07-01 00:00:00'', ''America/Los_Angeles'')

Local a UTC es así:

SELECT Tzdb.LocalToUtc(''2015-07-01 00:00:00'', ''America/Los_Angeles'', 1, 1)

Las opciones numéricas son indicadores para controlar el comportamiento cuando los valores de tiempo locales se ven afectados por el horario de verano. Estos se describen en detalle en la documentación del proyecto.

Answer 4

Sí, hasta cierto punto como se detalla here .
El enfoque que he usado (antes de 2008) es hacer la conversión en la lógica de negocios .NET antes de insertar en la base de datos.

Answer 5

SQL Server 2008 tiene un tipo llamado datetimeoffset . Es realmente útil para este tipo de cosas.

http://msdn.microsoft.com/en-us/library/bb630289.aspx

Luego puede usar la función SWITCHOFFSET para moverla de una zona horaria a otra, pero manteniendo el mismo valor UTC.

http://msdn.microsoft.com/en-us/library/bb677244.aspx

Robar

Answer 6

Si bien algunas de estas respuestas lo llevarán al estadio, no puede hacer lo que está tratando de hacer con fechas arbitrarias para SqlServer 2005 y anteriores debido al horario de verano. Usar la diferencia entre el UTC actual local y el actual me dará el desplazamiento tal como existe hoy. No he encontrado una manera de determinar cuál habría sido el desplazamiento para la fecha en cuestión.

Dicho esto, sé que SqlServer 2008 ofrece algunas nuevas funciones de fecha que pueden abordar ese problema, pero las personas que usan una versión anterior deben ser conscientes de las limitaciones.

Nuestro enfoque es persistir en UTC y realizar la conversión en el lado del cliente donde tenemos más control sobre la precisión de la conversión.

Answer 7

Tiendo a inclinarme hacia el uso de DateTimeOffset para todo el almacenamiento de fecha y hora que no esté relacionado con un evento local (es decir: reunión / fiesta, etc., de 12 p.m. a 3 p.m. en el museo).

Para obtener el DTO actual como UTC:

DECLARE @utcNow DATETIMEOFFSET = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME())
DECLARE @utcToday DATE = CONVERT(DATE, @utcNow);
DECLARE @utcTomorrow DATE = DATEADD(D, 1, @utcNow);
SELECT  @utcToday [today]
        ,@utcTomorrow [tomorrow]
        ,@utcNow [utcNow]

NOTA: Siempre usaré UTC al enviar por el cable ... el JS del lado del cliente puede llegar fácilmente al UTC local. Ver: new Date().toJSON() ...

El siguiente JS se encargará de analizar una fecha UTC / GMT en formato ISO8601 a un horario local.

if (typeof Date.fromISOString != ''function'') {
  //method to handle conversion from an ISO-8601 style string to a Date object
  //  Date.fromISOString("2009-07-03T16:09:45Z")
  //    Fri Jul 03 2009 09:09:45 GMT-0700
  Date.fromISOString = function(input) {
    var date = new Date(input); //EcmaScript5 includes ISO-8601 style parsing
    if (!isNaN(date)) return date;

    //early shorting of invalid input
    if (typeof input !== "string" || input.length < 10 || input.length > 40) return null;

    var iso8601Format = /^(/d{4})-(/d{2})-(/d{2})((([T ](/d{2}):(/d{2})(:(/d{2})(/.(/d{1,12}))?)?)?)?)?([Zz]|([-+])(/d{2})/:?(/d{2}))?$/;

    //normalize input
    var input = input.toString().replace(/^/s+/,'''').replace(//s+$/,'''');

    if (!iso8601Format.test(input))
      return null; //invalid format

    var d = input.match(iso8601Format);
    var offset = 0;

    date = new Date(+d[1], +d[2]-1, +d[3], +d[7] || 0, +d[8] || 0, +d[10] || 0, Math.round(+("0." + (d[12] || 0)) * 1000));

    //use specified offset
    if (d[13] == ''Z'') offset = 0-date.getTimezoneOffset();
    else if (d[13]) offset = ((parseInt(d[15],10) * 60) + (parseInt(d[16],10)) * ((d[14] == ''-'') ? 1 : -1)) - date.getTimezoneOffset();

    date.setTime(date.getTime() + (offset * 60000));

    if (date.getTime() <= new Date(-62135571600000).getTime()) // CLR DateTime.MinValue
      return null;

    return date;
  };
}

Answer 8

Uso de muestra:

SELECT
    Getdate=GETDATE()
    ,SysDateTimeOffset=SYSDATETIMEOFFSET()
    ,SWITCHOFFSET=SWITCHOFFSET(SYSDATETIMEOFFSET(),0)
    ,GetutcDate=GETUTCDATE()
GO

Devoluciones:

Getdate SysDateTimeOffset   SWITCHOFFSET    GetutcDate
2013-12-06 15:54:55.373 2013-12-06 15:54:55.3765498 -08:00  2013-12-06 23:54:55.3765498 +00:00  2013-12-06 23:54:55.373

Answer 9

7 años pasaron y ...
de hecho, existe esta nueva característica de SQL Server 2016 que hace exactamente lo que necesita.
Se llama AT TIME ZONE y convierte la fecha en un huso horario específico teniendo en cuenta los cambios de horario de verano (horario de verano).
Más información aquí: https://msdn.microsoft.com/en-us/library/mt612795.aspx

Answer 10

Esto funciona para las fechas que actualmente tienen el mismo desplazamiento UTC que el servidor SQL Server; no tiene en cuenta los cambios en el horario de verano. Reemplace YOUR_DATE con la fecha local para convertir.

SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);