ngx-bootstrap timepicker time zone offset causing SQL to save wrong time

Question

Using the timepicker module with all default options except for minutestep being set to 1.

code line is as follows

let DailyStartTime =  this.dailyStartTime != null ? 
                         new Date(this.dailyStartTime.getFullYear(),
                               this.dailyStartTime.getMonth(),
                               this.dailyStartTime.getDate(),
                               this.dailyStartTime.getHours(),
                               this.dailyStartTime.getMinutes()) : null

records the data as Wed May 31 2017 19:51:00 GMT-0400 (Eastern Daylight Time)

which displays initially, but once saved back to SQL it converts to "2017-05-31T23:51:00" which is advancing by the time offset. How can I negate this?

Answer 1

I had the same problem when trying to save an reactive form using ngx-bootstrap (3.0) timepicker to an POST (C# WebApi with EntityFramework)

Seems like a problem with UTC and local computer. The date from datepicker seems fine, but when creating the POST object javascript seems to adjust the timezone difference between UTC and local computer.

Workaround (.ts):

submit() {
  this.dataService.saveOpenHours({ OpensAt: this.fixDate(this.form.value.openHour), ClosesAt: this.fixDate(this.form.value.closeHour) }).subscribe(data => {
    // do something
  });
}

fixDate(d: Date): Date {
  d.setHours(d.getHours() - d.getTimezoneOffset() / 60);
  return d;
}

HTML

<form id="ngForm" [formGroup]="form" (ngSubmit)="submit()">
  <timepicker formControlName="openHour"></timepicker>
  <timepicker formControlName="closeHour"></timepicker>
</form>

Hopefully this will help someone!

Answer 2

I've been experiencing the same "problem" within the last couple of days.

I'm using Angular 7 for front end and .Net Core Web API for back end.

Web API is returning datetimes in JSON payload to the client as strings in this format:

2020-04-23T01:00:00

The TypeScript/JavaScript object model retains the data type of the date string as a string type.

When the bsDatepicker and timepicker have been attached as per the below example code (using two way model binding in this case), the type of the date string in the model is changed from a string type to a JavaScript Date type.

<input name="AppliesEndDate{{index}}" type="text" class="form-control form-control-sm" bsDatepicker [(ngModel)]="costCentre.appliesEndDate" [disabled]="costCentre.totalActiveAssignments!==0" />
<timepicker [(ngModel)]="costCentre.appliesEndDate" [showMeridian]="showMeridian" [disabled]="costCentre.totalActiveAssignments!==0" [minuteStep]="1"></timepicker>

When the model is sent back to the Web API server via HTTP POST or PUT the Date data type on object model is being serialized back to a string as a UTC formatted date as denoted by the Z suffix:

2020-04-23T00:00:00.000Z

From this one can conclude that this is happening client side and isn't specifically related to Angular. It is perhaps more to do with the way that JavaScript is converting Date types to strings.

Take these examples. I have deliberately picked two dates. One is outside British Summer Time (GMT+0000) and the other inside British Summer time (GMT+0100)

var date1 = new Date('2019-02-01T14:14:00');
var date2 = new Date('2019-05-01T14:14:00');

Fri Feb 01 2019 14:14:00 GMT+0000 (Greenwich Mean Time)

Wed May 01 2019 14:14:00 GMT+0100 (British Summer Time)

Running the statements below to convert to an ISO string:

console.log('toISOString ' + date1.toISOString());
console.log('toISOString ' + date2.toISOString());

Yields the following results:

toISOString 2019-02-01T14:14:00.000Z

toISOString 2019-05-01T13:14:00.000Z

Both dates are converted to UTC format. Notice that the second example has a time of 13:14 rather than 14:14. This is actually correct as the 01/05/2019 is inside BST which is GMT+0100. So the true UTC is actually 13:14.

This would be true of any other locale/time zone. The UTC datetime would be calculated based on the locale/region of client.

Under the covers I believe that this is what is happening client side with Angular when posting model back in the payload to server. It is simply calling 'toISOString'.

You also get the same results with "JSON.stringify", which I believe is calling "toISOString" under the covers.

console.log(JSON.stringify(date1));
console.log(JSON.stringify(date2));

Try running some of these commands in Chrome Sources > Snippets or another JavaScript scratchpad.

var date1 = new Date('2019-02-01T14:14:00');
var date2 = new Date('2019-05-01T14:14:00');

console.log(date1);
console.log(date2);

console.log(JSON.stringify(date1));
console.log(JSON.stringify(date2));

console.log('toISOString ' + date1.toISOString());
console.log('toISOString ' + date2.toISOString());

console.log('toGMTString ' + date1.toGMTString());
console.log('toGMTString ' + date2.toGMTString());

console.log('toLocaleString ' + date1.toLocaleString());
console.log('toLocaleString ' + date2.toLocaleString());

console.log('toUTCString ' + date1.toUTCString());
console.log('toUTCString ' + date2.toUTCString());

console.log(date1.getTimezoneOffset());
console.log(date2.getTimezoneOffset());

So, in conclusion we are getting UTC datetimes back from the client.

This is definitely a good thing and makes the datetimes locale/region agnostic.

Armed with this understanding there are workarounds if you want to store local datetimes in your database rather than UTC datetimes.

The client side code suggestion above certainly "fixes" the offset but consider that the data will still be serialized back to the client as a UTC date string. Be removing the offset this way you have actually broken the UTC date. On the server, Web API will still receive the Z suffix and this treat it as a UTC date. So you will actually have adjusted the date offset but not the context of the UTC format.

fixDate(d: Date): Date {
  d.setHours(d.getHours() - d.getTimezoneOffset() / 60);
  return d;
}

However, if you were to send the date back as a string (convert it yourself and remove the Z suffix) back to this format:

2020-04-23T01:00:00

Then you wouldn't be implying that the date was a UTC date.

Considering all of the above, my view (after a few days of looking into this) is that it is best to leave the client to serialize Date types as UTC. Accept that this is how it works and that always returning UTC string dates is a good thing from a consistency perspective.

In terms of the server side (Web API in my case) the date can be received as a UTC date and either stored in your database/datastore as a UTC date OR you can convert it server side to any locale/region of your choice. Remember that UTC is region agnostic, which is a good thing for global applications.

In my case, I opted to convert the incoming UTC datetimes to local datetimes using the following code snippet prior to storage in our database.

o.AppliesStartDate.ToLocalTime();

I hope this helps somebody and hopefully clarifies how things work. Two days ago I didn't get this at all...but it kind of makes sense now.

You can also do things like on Web API C#:

TimeZoneInfo ist = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");
TimeZoneInfo gmt = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");

var a1 = new DateTime(2019, 5, 30, 12, 30, 30, DateTimeKind.Utc);
Console.WriteLine(TimeZoneInfo.ConvertTimeFromUtc(a1, gmt));
Console.WriteLine(TimeZoneInfo.ConvertTimeFromUtc(a1, ist ));

This might further clarify that the dates are received on the Web API as UTC date kinds. If you check the "kind" property on the de-serialised datetime structure Web API side you will see "UTC".

I hope this helps you.