How do I globally serialize and deserialize Date vs DateTime when stored as UTC?
In my SQL Server database, I store all DateTime values as UTC. There are some instances, though, where I do not care about time like when a user in some random timezone selects a date using a DatePicker. In these cases, it seemed to make more sense to just store as Date vs DateTime.
When getting the dates out of the database and sending them down to my Angular app through Web API, I wanted to ensure all my DateTime values were formatted in a way so that Angular would know they were UTC dates and display as local time so I added this to Web API to add the "Z" at the end:
// Set all dates to UTC
config.Formatters.JsonFormatter.SerializerSettings.Converters.Add(new IsoDateTimeConverter
{
DateTimeFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"
});
This works, but the problem is that this conversion is also being applied to my Date values as well. When a date is pulled from the database into C#, they are in DateTime variables as C# doesn't have a Date variable so they get a time of midnight. So if a user in CST picks a date of 11/24/2018, when it goes round-trip to the database and back, it becomes 11/23/2018 (with the time of 6:00pm truncated).
How can I prevent this behavior? Some ideas could be:
- Conditionally apply the IsoDateTimeConverter?
- I don't think this is possible globally since I wouldn't be able to tell if it was a
DateorDateTimevalue stored in the C# DateTime variable.
- I don't think this is possible globally since I wouldn't be able to tell if it was a
- Change the datatype in SQL Server to
DateTime?
- I don't think this would work either because even if I stored the time based on where the data originated, couldn't it still be wrong for users in other timezones?
c#
sql-server datetime asp.net-web-api json.net asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
|
show 5 more comments
In my SQL Server database, I store all DateTime values as UTC. There are some instances, though, where I do not care about time like when a user in some random timezone selects a date using a DatePicker. In these cases, it seemed to make more sense to just store as Date vs DateTime.
When getting the dates out of the database and sending them down to my Angular app through Web API, I wanted to ensure all my DateTime values were formatted in a way so that Angular would know they were UTC dates and display as local time so I added this to Web API to add the "Z" at the end:
// Set all dates to UTC
config.Formatters.JsonFormatter.SerializerSettings.Converters.Add(new IsoDateTimeConverter
{
DateTimeFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"
});
This works, but the problem is that this conversion is also being applied to my Date values as well. When a date is pulled from the database into C#, they are in DateTime variables as C# doesn't have a Date variable so they get a time of midnight. So if a user in CST picks a date of 11/24/2018, when it goes round-trip to the database and back, it becomes 11/23/2018 (with the time of 6:00pm truncated).
How can I prevent this behavior? Some ideas could be:
- Conditionally apply the IsoDateTimeConverter?
- I don't think this is possible globally since I wouldn't be able to tell if it was a
DateorDateTimevalue stored in the C# DateTime variable.
- I don't think this is possible globally since I wouldn't be able to tell if it was a
- Change the datatype in SQL Server to
DateTime?
- I don't think this would work either because even if I stored the time based on where the data originated, couldn't it still be wrong for users in other timezones?
c#
sql-server datetime asp.net-web-api json.net asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
BTW you can use DateTimeFormat "o" for ISO8601 format dates. That doesn't help much with your question, of course.
– John
Nov 24 '18 at 13:07
Since you're using JSON.NET, you could maybe use the [JsonConverter] attribute on just those date fields. I'd be interested to see if there's a better solution.
– John
Nov 24 '18 at 13:10
@John, I thought about doing that. I'd prefer to handle it globally if possible. If I did end up doing this, do you know if the attribute would override the global IsoDateTimeConverter so I can still use the global converter for all other DateTime values?
– adam0101
Nov 24 '18 at 13:20
I believe it should do, but don't hold me to that.
– John
Nov 24 '18 at 13:22
1
First, it seems that you are confusing UTC with ISO8601. The first is a special Time Zone, the second one is a standard specification of string representations of date / time / datetime and periods. Second, If you are working with Sql Server, you should be usingDateTime2, and notDateTime.
– Zohar Peled
Nov 29 '18 at 7:52
|
show 5 more comments
In my SQL Server database, I store all DateTime values as UTC. There are some instances, though, where I do not care about time like when a user in some random timezone selects a date using a DatePicker. In these cases, it seemed to make more sense to just store as Date vs DateTime.
When getting the dates out of the database and sending them down to my Angular app through Web API, I wanted to ensure all my DateTime values were formatted in a way so that Angular would know they were UTC dates and display as local time so I added this to Web API to add the "Z" at the end:
// Set all dates to UTC
config.Formatters.JsonFormatter.SerializerSettings.Converters.Add(new IsoDateTimeConverter
{
DateTimeFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"
});
This works, but the problem is that this conversion is also being applied to my Date values as well. When a date is pulled from the database into C#, they are in DateTime variables as C# doesn't have a Date variable so they get a time of midnight. So if a user in CST picks a date of 11/24/2018, when it goes round-trip to the database and back, it becomes 11/23/2018 (with the time of 6:00pm truncated).
How can I prevent this behavior? Some ideas could be:
- Conditionally apply the IsoDateTimeConverter?
- I don't think this is possible globally since I wouldn't be able to tell if it was a
DateorDateTimevalue stored in the C# DateTime variable.
- I don't think this is possible globally since I wouldn't be able to tell if it was a
- Change the datatype in SQL Server to
DateTime?
- I don't think this would work either because even if I stored the time based on where the data originated, couldn't it still be wrong for users in other timezones?
c#
sql-server datetime asp.net-web-api json.net asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
In my SQL Server database, I store all DateTime values as UTC. There are some instances, though, where I do not care about time like when a user in some random timezone selects a date using a DatePicker. In these cases, it seemed to make more sense to just store as Date vs DateTime.
When getting the dates out of the database and sending them down to my Angular app through Web API, I wanted to ensure all my DateTime values were formatted in a way so that Angular would know they were UTC dates and display as local time so I added this to Web API to add the "Z" at the end:
// Set all dates to UTC
config.Formatters.JsonFormatter.SerializerSettings.Converters.Add(new IsoDateTimeConverter
{
DateTimeFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"
});
This works, but the problem is that this conversion is also being applied to my Date values as well. When a date is pulled from the database into C#, they are in DateTime variables as C# doesn't have a Date variable so they get a time of midnight. So if a user in CST picks a date of 11/24/2018, when it goes round-trip to the database and back, it becomes 11/23/2018 (with the time of 6:00pm truncated).
How can I prevent this behavior? Some ideas could be:
- Conditionally apply the IsoDateTimeConverter?
- I don't think this is possible globally since I wouldn't be able to tell if it was a
DateorDateTimevalue stored in the C# DateTime variable.
- I don't think this is possible globally since I wouldn't be able to tell if it was a
- Change the datatype in SQL Server to
DateTime?
- I don't think this would work either because even if I stored the time based on where the data originated, couldn't it still be wrong for users in other timezones?
c#
sql-server datetime asp.net-web-api json.net c#
sql-server datetime asp.net-web-api json.net asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
edited Nov 29 '18 at 20:44
adam0101
asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
asked Nov 24 '18 at 13:01
adam0101adam0101
12.6k1661124
12.6k1661124
BTW you can use DateTimeFormat "o" for ISO8601 format dates. That doesn't help much with your question, of course.
– John
Nov 24 '18 at 13:07
Since you're using JSON.NET, you could maybe use the [JsonConverter] attribute on just those date fields. I'd be interested to see if there's a better solution.
– John
Nov 24 '18 at 13:10
@John, I thought about doing that. I'd prefer to handle it globally if possible. If I did end up doing this, do you know if the attribute would override the global IsoDateTimeConverter so I can still use the global converter for all other DateTime values?
– adam0101
Nov 24 '18 at 13:20
I believe it should do, but don't hold me to that.
– John
Nov 24 '18 at 13:22
1
First, it seems that you are confusing UTC with ISO8601. The first is a special Time Zone, the second one is a standard specification of string representations of date / time / datetime and periods. Second, If you are working with Sql Server, you should be usingDateTime2, and notDateTime.
– Zohar Peled
Nov 29 '18 at 7:52
|
show 5 more comments
BTW you can use DateTimeFormat "o" for ISO8601 format dates. That doesn't help much with your question, of course.
– John
Nov 24 '18 at 13:07
Since you're using JSON.NET, you could maybe use the [JsonConverter] attribute on just those date fields. I'd be interested to see if there's a better solution.
– John
Nov 24 '18 at 13:10
@John, I thought about doing that. I'd prefer to handle it globally if possible. If I did end up doing this, do you know if the attribute would override the global IsoDateTimeConverter so I can still use the global converter for all other DateTime values?
– adam0101
Nov 24 '18 at 13:20
I believe it should do, but don't hold me to that.
– John
Nov 24 '18 at 13:22
1
First, it seems that you are confusing UTC with ISO8601. The first is a special Time Zone, the second one is a standard specification of string representations of date / time / datetime and periods. Second, If you are working with Sql Server, you should be usingDateTime2, and notDateTime.
– Zohar Peled
Nov 29 '18 at 7:52
BTW you can use DateTimeFormat "o" for ISO8601 format dates. That doesn't help much with your question, of course.
– John
Nov 24 '18 at 13:07
BTW you can use DateTimeFormat "o" for ISO8601 format dates. That doesn't help much with your question, of course.
– John
Nov 24 '18 at 13:07
Since you're using JSON.NET, you could maybe use the [JsonConverter] attribute on just those date fields. I'd be interested to see if there's a better solution.
– John
Nov 24 '18 at 13:10
Since you're using JSON.NET, you could maybe use the [JsonConverter] attribute on just those date fields. I'd be interested to see if there's a better solution.
– John
Nov 24 '18 at 13:10
@John, I thought about doing that. I'd prefer to handle it globally if possible. If I did end up doing this, do you know if the attribute would override the global IsoDateTimeConverter so I can still use the global converter for all other DateTime values?
– adam0101
Nov 24 '18 at 13:20
@John, I thought about doing that. I'd prefer to handle it globally if possible. If I did end up doing this, do you know if the attribute would override the global IsoDateTimeConverter so I can still use the global converter for all other DateTime values?
– adam0101
Nov 24 '18 at 13:20
I believe it should do, but don't hold me to that.
– John
Nov 24 '18 at 13:22
I believe it should do, but don't hold me to that.
– John
Nov 24 '18 at 13:22
1
1
First, it seems that you are confusing UTC with ISO8601. The first is a special Time Zone, the second one is a standard specification of string representations of date / time / datetime and periods. Second, If you are working with Sql Server, you should be using
DateTime2, and not DateTime.– Zohar Peled
Nov 29 '18 at 7:52
First, it seems that you are confusing UTC with ISO8601. The first is a special Time Zone, the second one is a standard specification of string representations of date / time / datetime and periods. Second, If you are working with Sql Server, you should be using
DateTime2, and not DateTime.– Zohar Peled
Nov 29 '18 at 7:52
|
show 5 more comments
1 Answer
1
active
oldest
votes
Using @John's suggestion, I created a custom IsoDateTimeConverter:
public class DateConverter : IsoDateTimeConverter
{
public DateConverter()
{
DateTimeFormat = "MM-dd-yyyy";
}
}
And manually applied it to each Date value which overrides the global converter I added to the Web API config.
[JsonConverter(typeof(DateConverter))]
public System.DateTime StartDate { get; set; }
I'm still hoping there's a global way of handling this instead of having to apply the attribute to each Date property, but this at least gets me unstuck.
A warning to Angular users. I originally tried to use the format "yyyy-MM-dd", but it must interpret that as UTC because I had the same issue when the date was shown to the user. Changing it to "MM-dd-yyyy" worked though. See https://github.com/angular/material2/issues/6111
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
I think you've got the right solution. If your classes used a different data type forDateversusDateTimeto mirror the DB then you could use a custom converter targeted at that type in your global config (alongside the other converter for DateTime) and you would not need the attributes. But since they use the same data type, you still need some kind of attribute to differentiate them in order to be able to apply a converter globally (e.g. using a custom contract resolver). So you might as well just use the[JsonConverter]attribute to apply the converter directly and skip the resolver.
– Brian Rogers
Nov 25 '18 at 17:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53458400%2fhow-do-i-globally-serialize-and-deserialize-date-vs-datetime-when-stored-as-utc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Using @John's suggestion, I created a custom IsoDateTimeConverter:
public class DateConverter : IsoDateTimeConverter
{
public DateConverter()
{
DateTimeFormat = "MM-dd-yyyy";
}
}
And manually applied it to each Date value which overrides the global converter I added to the Web API config.
[JsonConverter(typeof(DateConverter))]
public System.DateTime StartDate { get; set; }
I'm still hoping there's a global way of handling this instead of having to apply the attribute to each Date property, but this at least gets me unstuck.
A warning to Angular users. I originally tried to use the format "yyyy-MM-dd", but it must interpret that as UTC because I had the same issue when the date was shown to the user. Changing it to "MM-dd-yyyy" worked though. See https://github.com/angular/material2/issues/6111
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
I think you've got the right solution. If your classes used a different data type forDateversusDateTimeto mirror the DB then you could use a custom converter targeted at that type in your global config (alongside the other converter for DateTime) and you would not need the attributes. But since they use the same data type, you still need some kind of attribute to differentiate them in order to be able to apply a converter globally (e.g. using a custom contract resolver). So you might as well just use the[JsonConverter]attribute to apply the converter directly and skip the resolver.
– Brian Rogers
Nov 25 '18 at 17:26
add a comment |
Using @John's suggestion, I created a custom IsoDateTimeConverter:
public class DateConverter : IsoDateTimeConverter
{
public DateConverter()
{
DateTimeFormat = "MM-dd-yyyy";
}
}
And manually applied it to each Date value which overrides the global converter I added to the Web API config.
[JsonConverter(typeof(DateConverter))]
public System.DateTime StartDate { get; set; }
I'm still hoping there's a global way of handling this instead of having to apply the attribute to each Date property, but this at least gets me unstuck.
A warning to Angular users. I originally tried to use the format "yyyy-MM-dd", but it must interpret that as UTC because I had the same issue when the date was shown to the user. Changing it to "MM-dd-yyyy" worked though. See https://github.com/angular/material2/issues/6111
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
I think you've got the right solution. If your classes used a different data type forDateversusDateTimeto mirror the DB then you could use a custom converter targeted at that type in your global config (alongside the other converter for DateTime) and you would not need the attributes. But since they use the same data type, you still need some kind of attribute to differentiate them in order to be able to apply a converter globally (e.g. using a custom contract resolver). So you might as well just use the[JsonConverter]attribute to apply the converter directly and skip the resolver.
– Brian Rogers
Nov 25 '18 at 17:26
add a comment |
Using @John's suggestion, I created a custom IsoDateTimeConverter:
public class DateConverter : IsoDateTimeConverter
{
public DateConverter()
{
DateTimeFormat = "MM-dd-yyyy";
}
}
And manually applied it to each Date value which overrides the global converter I added to the Web API config.
[JsonConverter(typeof(DateConverter))]
public System.DateTime StartDate { get; set; }
I'm still hoping there's a global way of handling this instead of having to apply the attribute to each Date property, but this at least gets me unstuck.
A warning to Angular users. I originally tried to use the format "yyyy-MM-dd", but it must interpret that as UTC because I had the same issue when the date was shown to the user. Changing it to "MM-dd-yyyy" worked though. See https://github.com/angular/material2/issues/6111
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
Using @John's suggestion, I created a custom IsoDateTimeConverter:
public class DateConverter : IsoDateTimeConverter
{
public DateConverter()
{
DateTimeFormat = "MM-dd-yyyy";
}
}
And manually applied it to each Date value which overrides the global converter I added to the Web API config.
[JsonConverter(typeof(DateConverter))]
public System.DateTime StartDate { get; set; }
I'm still hoping there's a global way of handling this instead of having to apply the attribute to each Date property, but this at least gets me unstuck.
A warning to Angular users. I originally tried to use the format "yyyy-MM-dd", but it must interpret that as UTC because I had the same issue when the date was shown to the user. Changing it to "MM-dd-yyyy" worked though. See https://github.com/angular/material2/issues/6111
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
answered Nov 25 '18 at 14:55
adam0101adam0101
12.6k1661124
12.6k1661124
I think you've got the right solution. If your classes used a different data type forDateversusDateTimeto mirror the DB then you could use a custom converter targeted at that type in your global config (alongside the other converter for DateTime) and you would not need the attributes. But since they use the same data type, you still need some kind of attribute to differentiate them in order to be able to apply a converter globally (e.g. using a custom contract resolver). So you might as well just use the[JsonConverter]attribute to apply the converter directly and skip the resolver.
– Brian Rogers
Nov 25 '18 at 17:26
add a comment |
I think you've got the right solution. If your classes used a different data type forDateversusDateTimeto mirror the DB then you could use a custom converter targeted at that type in your global config (alongside the other converter for DateTime) and you would not need the attributes. But since they use the same data type, you still need some kind of attribute to differentiate them in order to be able to apply a converter globally (e.g. using a custom contract resolver). So you might as well just use the[JsonConverter]attribute to apply the converter directly and skip the resolver.
– Brian Rogers
Nov 25 '18 at 17:26
I think you've got the right solution. If your classes used a different data type for
Date versus DateTime to mirror the DB then you could use a custom converter targeted at that type in your global config (alongside the other converter for DateTime) and you would not need the attributes. But since they use the same data type, you still need some kind of attribute to differentiate them in order to be able to apply a converter globally (e.g. using a custom contract resolver). So you might as well just use the [JsonConverter] attribute to apply the converter directly and skip the resolver.– Brian Rogers
Nov 25 '18 at 17:26
I think you've got the right solution. If your classes used a different data type for
Date versus DateTime to mirror the DB then you could use a custom converter targeted at that type in your global config (alongside the other converter for DateTime) and you would not need the attributes. But since they use the same data type, you still need some kind of attribute to differentiate them in order to be able to apply a converter globally (e.g. using a custom contract resolver). So you might as well just use the [JsonConverter] attribute to apply the converter directly and skip the resolver.– Brian Rogers
Nov 25 '18 at 17:26
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53458400%2fhow-do-i-globally-serialize-and-deserialize-date-vs-datetime-when-stored-as-utc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
BTW you can use DateTimeFormat "o" for ISO8601 format dates. That doesn't help much with your question, of course.
– John
Nov 24 '18 at 13:07
Since you're using JSON.NET, you could maybe use the [JsonConverter] attribute on just those date fields. I'd be interested to see if there's a better solution.
– John
Nov 24 '18 at 13:10
@John, I thought about doing that. I'd prefer to handle it globally if possible. If I did end up doing this, do you know if the attribute would override the global IsoDateTimeConverter so I can still use the global converter for all other DateTime values?
– adam0101
Nov 24 '18 at 13:20
I believe it should do, but don't hold me to that.
– John
Nov 24 '18 at 13:22
1
First, it seems that you are confusing UTC with ISO8601. The first is a special Time Zone, the second one is a standard specification of string representations of date / time / datetime and periods. Second, If you are working with Sql Server, you should be using
DateTime2, and notDateTime.– Zohar Peled
Nov 29 '18 at 7:52