MSSQL i czas UTC

[arturs]

Witam..
Mam taki problem
jest sobie baza w MSSQLu (2008) w której autor wykombinował sobie że
czas będzie zapisywał jako czas UnixDateTime

napisałem sobie funkcję która to pokazuje mi jako datę i godzinę w
zapisie strawnym dla człowieka..

CREATE function [dbo].[data] (@unixtime as int )
returns varchar(20)
as
begin
return(
convert(varchar(20), DateAdd(second, @unixtime, '19700101'), 120)
)

end

nie ma tu żadnej filozofii

problem pojawił się z następną wersją bazy, otóż pole jest to samo,
zapisywane tak samo ale czas jest UTC.. i teraz problem jest taki że w
raportach jest czas przesunięty (w lato o 2 godziny, a w zimę będzie o
godzinę), o ile przekształcanie aktualnego czasu na UTC da się jedną
funkcją zrealizować to nie bardzo kojarzę jak zrobić odwrotnie - czyli
jak czas UTC przedstawić jako lokalny.
Najlepiej by było jakby można zmodyfikować powyższą funkcję tak żeby w
niej sprawdzać dla podanej daty parametry do przeliczenia na nas lokalny..
Ma ktoś jakiś pomysł?

Pozdrawiam

[Jacek]

Myśle, że najprościej będzie zrobić funkcje w C# czyli .net CLR.
Przypuszczam, że takie rzeczy są tam w funkcjach systemowych.

[arturs]

Post by Jacek
Myśle, że najprościej będzie zrobić funkcje w C# czyli .net CLR.
Przypuszczam, że takie rzeczy są tam w funkcjach systemowych.

Hmm.. tyle że nie mam o tym pojęcia i wolałbym w samym SQLu..
Aha - mam dodatkową tabelę gdzie jest zapisana strefa czasowa dla
obiektu oraz dodatkowa informacja czy uwzględniać czas letni..

[Jacek]

Post by arturs

Post by Jacek
Myśle, że najprościej będzie zrobić funkcje w C# czyli .net CLR.
Przypuszczam, że takie rzeczy są tam w funkcjach systemowych.

Hmm.. tyle że nie mam o tym pojęcia i wolałbym w samym SQLu..
Aha - mam dodatkową tabelę gdzie jest zapisana strefa czasowa dla obiektu
oraz dodatkowa informacja czy uwzględniać czas letni..

Tu masz linka
http://www.mssqltips.com/sqlservertip/2339/converting-utc-to-local-time-with-sql-server-clr/
Sprawa jest prosta
Z ciekawości możesz porównać wydajność do SQL (jeżeli to istotne)

[PaSkol]

Post by Jacek
Myśle, że najprościej będzie zrobić funkcje w C# czyli .net CLR.
Przypuszczam, że takie rzeczy są tam w funkcjach systemowych.

Łomatko.

[PaSkol]

Post by arturs
problem pojawił się z następną wersją bazy, otóż pole jest to samo,
zapisywane tak samo ale czas jest UTC.. i teraz problem jest taki że w
raportach jest czas przesunięty (w lato o 2 godziny, a w zimę będzie o
godzinę), o ile przekształcanie aktualnego czasu na UTC da się jedną
funkcją zrealizować to nie bardzo kojarzę jak zrobić odwrotnie - czyli
jak czas UTC przedstawić jako lokalny.
Najlepiej by było jakby można zmodyfikować powyższą funkcję tak żeby w
niej sprawdzać dla podanej daty parametry do przeliczenia na nas lokalny..
Ma ktoś jakiś pomysł?

O tempora, o mores!

Przecież to jest banalne! Wystarczy ustalić obecny czas oraz czas w UTC,
odjąć pierwszy od drugiego i różnicę dodać do czasu w UTC. Ludzie, upał
wam na głowy siadł?!

create function dbo.UnixSecondsToDateTime (@unixtime int)
returns datetime
as
begin
set @unixtime = @unixtime + DateDiff(ss, sysUTCDateTime(), GetDate());
return DateAdd(ss, @unixtime, '19700101');
end;

I funkcja powinna zwracać czas (datetime), a nie tekst. Formatowanie
daty to zadanie dla raportu, a nie dla bazy danych.

[arturs]

Post by PaSkol
Przecież to jest banalne! Wystarczy ustalić obecny czas oraz czas w UTC,
odjąć pierwszy od drugiego i różnicę dodać do czasu w UTC. Ludzie, upał
wam na głowy siadł?!
returns datetime
as
begin
end;

Taa...
a gdzieś napisałem że ma to działać na BIEŻĄCYM czasie?
a o czasie letnim i zimowym kolega słyszał? napisałem o tym w pierwszym
poście, dobra żeby nie było - wygooglowałem takie coś:
funkcja z moimi małymi modyfikacjami bo w oryginale były źle znaki
większości i mniejszości przy porównaniu i niektóre rzeczy się powtarzały:


/*Bruce T Change from UTC TimeZone SQL Function
Modified by Justin Bennett
Modified by Steve Hiner
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx */

/* Input Coordinated Universal Time (UTC) DATETIME, desired timezone and
1 to enable DST - Example: print dbo.UTCtoLocalDate('3/11/2007
12:00:00', -8, 1) for PST
Example: print dbo.UTCtoLocalDate ('3/11/2007 12:00:00', -3.5, 1) for NST*/

ALTER FUNCTION [dbo].[UTCtoLocalDate]
(
@UTCDate DATETIME
, @TZ NUMERIC(3,1)
, @DST INT
)
RETURNS DATETIME
AS
BEGIN
/* If the timzone does not already equal GMT 0 */
IF ( @TZ <> 0 ) or (@DST = 1)
BEGIN
DECLARE @LocalDate DATETIME
DECLARE @UTCDelta NUMERIC(5,1)

IF (@DST = 1)
BEGIN
DECLARE @thisYear INT
DECLARE @DSTDay INT
DECLARE @NormalDay INT
DECLARE @DSTDate DATETIME
DECLARE @NormalDate DATETIME

SET @thisYear = CONVERT(INT, DATEPART(yyyy, @UTCDate))

/* DST for 2007 and after */

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )
SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = CONVERT(DATETIME, '3/' + CONVERT(varchar(2), @DSTDay) +
'/' + CONVERT(varchar(4), @thisYear) + ' 2:00:00.000 AM' )
SET @NormalDate = CONVERT(DATETIME, '11/' + CONVERT(varchar(2),
@NormalDay) + '/' + CONVERT(varchar(4), @thisYear) + ' 2:00:00.000 AM' )
--print @DSTDate
--print @NormalDate



IF ((@UTCDate < @DSTDate) AND (@UTCDate > @NormalDate))
/* Adjust timezone if in DST Period */
BEGIN
SET @TZ = @TZ + 1
END
SET @UTCDelta = @TZ * 60
END

/* now CONVERT UTC date to local date */
SET @LocalDate = DATEADD(minute, @UTCDelta, @UTCDate)
END
ELSE
/* If the timzone already equals GMT 0 without DST */
BEGIN
SET @LocalDate = @UTCDate
END


RETURN(@LocalDate)
END

Działa ok, sprawdza czy podana data jest w zakresie czasu letniego i
przelicza - wydaje się ze prawidłowo

Post by PaSkol
I funkcja powinna zwracać czas (datetime), a nie tekst. Formatowanie
daty to zadanie dla raportu, a nie dla bazy danych.

Wszystko jedno co zwraca byle prawidłowo - tak jest łatwiej/szybciej bo
korzystają z tego co najmniej 4 różne systemy i nie tylko moje więc nie
ma co wnikać..

[PaSkol]

Post by arturs

Post by PaSkol
Przecież to jest banalne! Wystarczy ustalić obecny czas oraz czas w UTC,
odjąć pierwszy od drugiego i różnicę dodać do czasu w UTC. Ludzie, upał
wam na głowy siadł?!

Taa...
a gdzieś napisałem że ma to działać na BIEŻĄCYM czasie?

No i? Dalej ta sama zasada, choć już nie tak prosto, bo trzeba datę UTC
umiejscawiać w czasie letnim (i uwzględniać dodatkowe przesunięcie w
czasie). Niemniej sprawa nadal sprowadza się do dodawania przesunięcia
do czasu UTC, które to przesunięcie jest wyliczane w sposób bardziej
rozbudowany.