1
I have an SQL Server running on AWS, since yesterday the function GETDATE() has been returning the time with an extra hour as if it were in the time zone -02:00. I have checked on Linux that the date and time is correct and the time zone is set to America/Sao_Paulo.
Executing the following command EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName' returns "E. South America Standard Time", that on the table sys.time_zone_info is at the following values:
+-----------------------------+
| CURRENT_UTC_OFFSET | -02:00 |
| IS_CURRENTLY_DST | 1 |
+-----------------------------+
What can I do to correct this time difference?
if I understand correctly, the machine time is one (which you saw on linux) and sql is with this offset of -2hs, that’s it?
– Ricardo Pontual
Shouldn’t the IS_CURRENTLY_DST field be with 0? After all we are no longer in daylight saving time.
– anonimo
@Ricardopunctual, in Linux is -3, but in SQL Server shows as -2
– Sergio Rodrigues
I couldn’t find a way to change the value
IS_CURRENTLY_DSTto 0– Sergio Rodrigues
And why not simply use everything in UTC and in the application set the user’s local time zone? Thus making the bank work anywhere in the world and avoid conflicts with the 4 different time zones of Brazil (yes Brazil has different time zones)?
– Guilherme Nascimento
@Guilhermenascimento In my Christ, I gave one
alert(new Date('11/11/2019 03:00:00'))and appearedMon Nov 11 2019 03:00:00 GMT-0200 (Horário de Verão de Brasília). That is, the browser time zone (that would be the user) is not reliable.– Victor Stafusa
@Victorstafusa regardless of this, the server does not always have to know the client’s time, and that’s EXACTLY what I’m saying, read (SELECT) something from the server and parse in the "front-end" It can even go wrong if the PC is having battery problems or the synchronization fails because some country forgot to send the time zone to those responsible (as happened with Brazil, which gave that big problem). That is, Life’s updates/updates/Places if it is possible to do everything in the bank without receiving anything from the customer [...]
– Guilherme Nascimento
[...] and the "parse" and front-end adjustment of what was received may even "fail", but it will not fail because it is wrong in the bank, will fail because of the local equipment. And another thing, this shape of your time spent in
Datecode is wrong, Javascript is not guesswork, if you use a format like this2019-11-11T11:03:00.000Z, see the result: https://i.stack.Imgur.com/dzFBW.png, or you passed a "loose" format that the browser will make an effort to guess, but the most you will get is to assume that it is local time anyway.– Guilherme Nascimento
@Guilhermenascimento I know that there are ways to deal with this in javascript and that there are numerous ways to specify Timezone. I’m just saying that this is an unfortunately very common problem and that the client’s time zone is also unreliable in relation to daylight saving time.
– Victor Stafusa
@Victorstafusa but there is "local problem" and the most you could do to solve is to have a user area that he inform the "time zone of it" for the back-end application read from the bank make the adjustment and return in the "download"... If the person with local problem in your device will not influence the server side, soon its problem will be all on the equipment itself, which probably even some certificates or authentications do not work ... once the battery of a PC board has crashed, marking "2001" always, almost no program (using internet) and website opened.
– Guilherme Nascimento
@Guilhermenascimento The problem is that the latest version of Chrome gives the wrong time zone to half of Brazil from the day 03/11, and the lay users will get quite lost. Expect to see a rain of questions about daylight saving errors from this date.
– Victor Stafusa
@Victorstafusa I’m not aware of this, if it’s based on the code you posted in the first comment, as I said, it’s wrong, the JS takes local time and doesn’t make up the difference. Now if you can cite the problem of Chorme with details, or some link may be interesting. However I mentioned client-side only as example the browser, client-side could be anything and if such bug exists then soon will fix, but as far as I know browsers are based on local machine time, I don’t think Chrome has its own schedule or that they would commit such a gaffe.
– Guilherme Nascimento
@Guilhermenascimento The same code works correctly in Firefox. Chrome picks up the OS time zone, but the DST table comes from somewhere else. I’m looking for Reports bugs on this.
– Victor Stafusa
@Victorstafusa understand, but anyway it is as I said, it is a local problem and does not affect the server, what will give SERIOUS problem is the person insist to record in the America/Brazilia time at the bank and then conflict with a number of problems of different zones in brazil (we have 4 mergers as I explained) and if this application/bank is "eventually synchronized" with international third party services. And this is the whole purpose of my first comment, to avoid the serious and "irreversible problem" [...]
– Guilherme Nascimento
[...] And if you still disagree to do the parse of the UTC received on the front the suggestion of "solution" I have already mentioned in this comment: https://answall.com/questions/417475/sql-server-hor%C3%a1rio-incorrect-na-fun%C3%A7%C3%a3o-getdate-Timezone? noredirect=1#comment813610_417475
– Guilherme Nascimento