Understanding the right DateTime Parameter to use when inserting Data into LIVE database

Question

I have two issues that have been giving me headache in solving.

The first is: Upon successful login by a user, an update is done in the Logindate column of the database table. But before the update is done, the value of the Logindate column is fetched and sent to the next page, using Session to be displayed as Last Login date and time. This date and time will be converted to the user's local date and time using this code below.

*****The label control to display the Last Login Date and Time on the page is "TimeLbl".

May I please know if I did the correct thing in the below code, where I put the Session["LastLogin"]?

protected void Page_Load(object sender, EventArgs e)
        {
             string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT LastLogin FROM Users WHERE Uid= @Uid", con))
                {
                    cmd.Parameters.AddWithValue("@Uid", Session["user"]);
                    con.Open();
                    DateTime time1 = Convert.ToDateTime(Session["LastLogin"]);//This is where I put the Date and Time
                    TimeZoneInfo serverTimeZone = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"); // Get Server TimeZone                            
                    DateTime dateTime = TimeZoneInfo.ConvertTime(time1, serverTimeZone, TimeZoneInfo.Local); // Convert to local time            
                    Timelbl.Text = dateTime.ToString("dddd, MMMM d, yyyy h:mm tt");
                }
            }       
        }

Secondly, I learnt that DateTime.Now returns a DateTime value representing the current, system time (in whatever time zone the system is running in). The DateTime.Kind property will be DateTimeKind.Local.

Now my Question is, If I use DateTime.Now it returns value that represent the current system time, which is in local time. Does that mean that since I use DateTime.UtcNow, that's why it returned a time different from a user's local time?

I thought DateTime.Now only works when the application is run locally but when an application runs on a live server, the DateTime.Now does not matter, so I was told when I first hosted a website on a live server. I had the issue of different time zone displaying on my pages when I hosted it on a live server, which was why I changed to DateTime.UtcNow.

I have been using DateTime.UtcNow as Date Time parameter and getting a huge margin as regards time difference. So I don't know if I use DateTime.Now, if it will give me the correct time.

I will appreciate if I finally get to have great knowledge on this. Thank you.

Answer 1

it a little more complex. you have three time zones

1. the sql server timezone. this is based on the hosts timezone setting, sql server always assumes stored dates are in its local timezone. GETUTCDATE() returns the local time offset to GMT time. if you store this in a datetime/datetime2 column, when retrieved it will assume its a local time. use datetimeoffset if you want the date and timezone stored together.
2. the web server timezone. the web server code when its queries the data base assumes dates returned are in the web servers local timezone.
3. the browsers (users) timezone.

in the .net framework dates are number of ticks from 1/1/0001. Dates are assumed to be local time. the runtime has methods to convert between timezones.

your code:

// get gmt timezone (not server time zone unless server is GMT)
TimeZoneInfo serverTimeZone = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time"); 

// convert date assuming its GMT time to the web servers timezone                            
DateTime dateTime = TimeZoneInfo.ConvertTime(
    time1,               // some time
    serverTimeZone,      // treat as GMT time
    TimeZoneInfo.Local); // Convert to local web server time            
                  

will only work if the dates are stored in the database in GMT or the web server is in the GMT timezone.

as suggested above, it more common to GMT on the database and webserver dates. then have the user specify their timezone, and convert the datetime to the user timezone.

Answer 2

Your convert code is odd:

// local time - 3:44 PM Pacific Day Light Time
var d1 = "6/2/2023 3:44:00 PM";
var d2 = Convert.ToDateTime(d1);       
var d3 = d2.ToLocalTime().ToString(); //convert from UTC to PDT
Console.WriteLine(d1);     //3:44 PM 
Console.WriteLine(d2);     //3:44 PM
Console.WriteLine(d2);     //8:44 AM (PDT = UTC-07:00)

the convert assumed the date string is UTC, so the .ToLocalTime() is date -7 hours.

now to database:

My devbox is Pacific Day Light Time
My SqlServer is UTC time (docker container on my devbox)

so my devbox calling sql:

// local time - 3:44 PM PDT
using (var conn = await GetConnectionAsync())
{
    var query = @"select 
					@d1 as D1, 
					@d2 as D2, 
					getdate() as D3
				";
    var cmd = new SqlCommand(query, conn);
	cmd.Parameters.AddWithValue("@d1", DateTime.Now);
    cmd.Parameters.AddWithValue("@d2", DateTime.UtcNow);
    using (var reader = await cmd.ExecuteReaderAsync())
	{
		await reader.ReadAsync();
		Console.WriteLine(reader.GetDateTime(0)); //3:44 PM
        Console.WriteLine(reader.GetDateTime(1)); //10:44 PM
        Console.WriteLine(reader.GetDateTime(2)); //10:44 PM
    }
}

also remember as a web site, the browser can be a different time zone. the ToLocalTime() will always be the web servers local time. if hosted, its not likely to be your time zone.

Answer 3

Hi @AgaveJoe ,

This actually got it right.

private Location GetLocation()
        {
            string ipAddress;
            ipAddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
            if (ipAddress == "" || ipAddress == null)
                ipAddress = Request.ServerVariables["REMOTE_ADDR"];
            string APIKey = "MY_ACCESS_KEY";
            string url = string.Format("https://api.ip2location.io/?key={0}&ip={1}&format=json", APIKey, ipAddress);
            ServicePointManager.Expect100Continue = true;
            ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
            using (WebClient client = new WebClient())
            {
                string json = client.DownloadString(url);
                Location location = new JavaScriptSerializer().Deserialize<Location>(json);
                return location;
            }
        }

public string GetTimeZoneNameByOffsetTime(string offset)
        {
            return TimeZoneInfo.GetSystemTimeZones().FirstOrDefault(x => x.BaseUtcOffset == TimeSpan.Parse(offset)).StandardName;
        }

        public class Location
        {
            public string ip { get; set; }
            public string country_code { get; set; }
            public string country_name { get; set; }
            public string region_name { get; set; }
            public string city_name { get; set; }
            public double latitude { get; set; }
            public double longitude { get; set; }
            public string zip_code { get; set; }
            public string time_zone { get; set; }
            public string asn { get; set; }
            public string @as { get; set; }
            public bool is_proxy { get; set; }
        }

private void LastLogin()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT LastLogin FROM Users WHERE Id = @Id", con))
                {
                    cmd.Parameters.AddWithValue("@Id", Session["user"]);
                    con.Open();
                    DateTime time1 = Convert.ToDateTime(Session["LastLogin"]);
                    TimeZoneInfo serverTimeZone = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");
                    TimeZoneInfo userTimeZone = TimeZoneInfo.FindSystemTimeZoneById(GetTimeZoneNameByOffsetTime(this.GetLocation().time_zone.Replace("+", "")));
                    DateTime userLocalTime = TimeZoneInfo.ConvertTime(time1, serverTimeZone, userTimeZone);
                    Timelbl.Text = userLocalTime.ToString("dddd, MMMM d, yyyy h:mm tt");
                }
            }
        }