Store DateTime according to Current CultureInfo ASP.NET Core

Question

My application server in a location different from where most of my application users currently are.

While storing datetime values, I realize that if I simply do this

`TimeAdded=DateTime.Now`,

the Time is stored according to the server location time regardless of the fact that I have done the following in my Startup.cs class

 var cultureInfo = new CultureInfo("en-NG");
 cultureInfo.NumberFormat.CurrencySymbol = "NGN";

 CultureInfo.DefaultThreadCurrentCulture = cultureInfo;
 CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;

The location of my users is on UTC+1 while the server is currently located in a location that is on UTC-8

Is there a way to make my datetime values to be stored in the database (SQL Server) in accordance with the CurrentCultureInfo set in startup.cs class?

I have only currently resorted to storing the datetime Values as UTC and convert it on to the equivalent UTC+1 value after retrieving the values from the database.

So, I do

TimeAdded=DateTime.UtcNow;

And after retrieving the value for display to users, I convert it by

TimeAdded=TimeAdded.AddHours(1)

I wish to get a better way go about this is there is.

Thank you

No. When transferring DateTime between machines the standard is to save using UTC. A Datetime is a number that uses UTC. When you convert a string to/from a DateTime the local machine uses the Culture (time zone) to automatically convert to UTC. So what you are seeing is the date displayed using the machines timezone. A date is automatically stored using the current machine timezone. If you are storing dates from another timezone the string has to include the timezone or you have to specify the timezone. — jdweng, Dec 26 '20 at 14:46
You could change the timezone of the whole server to make it coincide with the one of the users. It is the only way. You can't change the timezone of a single app (see for example https://stackoverflow.com/questions/8589014/how-to-change-time-zone-for-an-asp-net-application) — xanatos, Dec 26 '20 at 14:57
Instead of storing DateTime data as UTC, another method is to use the `DateTimeOffset` type in both .NET and T-SQL. That way, you don't have to bother with Culture or date math (e.g. `DateTimeOffset.Now`). — Dan Guzman, Dec 26 '20 at 15:12
@jdweng, I am currently storing the date as UTC. But you mentioned that I can include or specify the while storing the dates, could you please help with a link to a resource where I can find how to do that? — Josh, Dec 26 '20 at 15:59
@Josh Sql Server supports a `DateTimeOffset` natively (https://learn.microsoft.com/it-it/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver15) — xanatos, Dec 26 '20 at 16:19
@xanatos, My problem is to be able to store the datetime value in the datetime offset different from that of the (database) server on the server as the values are being stored — Josh, Dec 26 '20 at 17:32
@Josh The `DateTimeOffset` of C# and of sql permit to set the offset and save it separately from the datetime. — xanatos, Dec 26 '20 at 17:35
@xanatos Thank you for that information. Please do you have any code samples of how to interact with that from a C# code to deal with the switching between the timezones while storing and retrieving/displaying data? — Josh, Dec 26 '20 at 18:10
@Josh No, I haven't ever used it. I know that exists but only because I read much. I don't think it is complicated to use. For example: `DateTimeOffset.Now.ToOffset(TimeSpan.FromHours(-8))` will generate a `Now` with offset -8... You should centralize the generation of DateTime in a method somewhere (`public static DateTimeOffset Now() { return DateTimeOffset.Now.ToOffset(TimeSpan.FromHours(-8)); }`, so that no one will forget to use it. Depending on how your program is written (if you have a base page for all the pages), you could put it there — xanatos, Dec 26 '20 at 18:16
@xanatos Thank you for that much effort to guide. I appreciate that. Let me see if I can figure out how to adapt it to my application — Josh, Dec 26 '20 at 18:34
One question. Are you using Razor pages or views to show the date to users? or are you getting the data from Web API? — Jesús López, Dec 28 '20 at 09:15
@JesúsLópez, I am using Views not Razor Pages. My application is an MVC application — Josh, Dec 28 '20 at 22:07

Jesús López · Answer 1 · 2020-12-28T10:54:05.820

Is there a way to make my datetime values to be stored in the database (SQL Server) in accordance with the CurrentCultureInfo?

Datetimes are stored in binary format in SQL Server without any culture information. However SQL Server can store date and time information with time offsets. SQL Server has the datetimeoffset type. I think storing dates with culture information doesn't solve the problem of showing the date and time in the local time zone of the user because culture info can correspond to more than one time zone. For example here in Spain we have two time zones and USA has several time zones.

And after retrieving the value for display to users, I convert it by

TimeAdded=TimeAdded.AddHours(1)

I wish to get a better way go about this is there is.

Don't do that, because what happens on daylight saving time? And you are assuming all your users are in the same time zone.

I recommend you to store the datetime in UTC because it's not ambiguous, it has only one interpretation. Then you have to translate that date time into the local date time of the user time zone for displaying. If you know the time zone of the user you can do the following:

var usertimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(userTimeZoneId);
var userLocalDateTime = TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, usertimeZoneInfo);

The IANA time zone of the user can be figured out using some JavaScript. This answer shows you how to do that.

And the IANA time zone can be translated to windows time zone id. Please, see this answer. It shows you how to do it.

On the other hand, if you are fetching the data by calling some Web API from the browser. you don't really need to do too much. You just need to make sure the data is serialized as it was UTC date. Yes, you are storing UTC dates on the database but when you retrieve them from the database you get DateTimeKind.Unspecified dates and then those dates are not serialized correctly to JSON. If you are using Newtosoft.Json to serialize, you can specify SerializerSettings.DateTimeZoneHandling = DateTimeZoneHandling.Utc

If you are using System.Text.Json then you have to write custom JsonConverter for DateTime.

Here you a have a JsonConverter for serializing DateTimeKind.Unspecified dates as they were UTC:

public class JsonDateConverter : JsonConverter<DateTime>
{
    public override DateTime Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
    {
        var d = reader.GetDateTime();
        return d;
    }

    public override void Write(Utf8JsonWriter writer, DateTime value, JsonSerializerOptions options)
    {
        if (value.Kind == DateTimeKind.Unspecified)
        {
            value = new DateTime(value.Ticks, DateTimeKind.Utc);
        }
        writer.WriteStringValue(value);
    }
}

Then you write some Javascript to parse dates just after the data has been retrieved from web API:

obj.theDate = obj.TheDate && new Date(obj.TheDate);

You need to do it because DateTime is serialized as string when serializing to JSON. And you want theDate to be a JavaScript date.

After that, theDate is the user time zone local time date. To check it write the following:

console.log(obj.theDate);

Store DateTime according to Current CultureInfo ASP.NET Core

1 Answers1