How to enable Time Zone support for ASP.NET Core API?-CodePudding

I've got a mobile app and a Web application that consumes an ASP.NET Core 3.x WEB API that uses SQL Server as it's datastore. The client applications can be in different Time Zones.

The dilemma I'm facing is filtering records with a date range.

For instance, I've got a table called 'PaymentTransaction' that consists of a column 'TransactionDateTime' that is of type DateTimeOffset.

Entity object:

[Table("PaymentTransaction")]
public class PaymentTransaction 
{
    public DateTimeOffset TransactionDateTime { get; set; }
}

API endpoint for creating the records: PaymentRequestDto content excluded for brevity

[HttpPost, Route("Create")]
public async Task<ActionResult> Create([Required] PaymentRequestDto)
{
    await _context.PayoutTransactions.AddAsync(new PaymentTransaction()
    {
        TransactionDateTime = DateTimeOffset.UtcNow;
    });

    await _context.SaveChangesAsync();    
    return Ok();
}

API endpoint for filtering the records:

[HttpGet, Route("GetRangeSample/{startDateTime}/{endDateTime}")]
public async Task<ActionResult> GetRangeSample([Required] DateTimeOffset startDateTime, [Required] DateTimeOffset endDateTime)
{
    var result = await _context.PaymentTransactions.Where(x => x.TransactionDateTime >= date && x.TransactionDateTime <= date).ToListAsync();
    return Ok(result);
}

JavaScript Client requesting filtered records;

var startDate = new Date();
var endDate = new Date();
endDate.setDate(endDate.getDate()   7)

$.ajax({
    type: "get",
    url: "http://localhost:55724/api/GetRangeSample",
    data:{
        startDateTime: startDate,
        endDateTime: endDate
    },
    success: function(response){
        console.log(response);
    },
    error: function(error)
    {
        console.log(error);
    }
});

If I'm to filter records for a given date range, let's say 02-21-2022 to 02-28-2022. some records are not returned.

What am I doing wrong? How do you normally go about saving a DateTimeOffset and retrieving records based on a date filter?

CodePudding user response：

Presuming you mean to have the user select entire days in their time zone, the issue is in the way you've created the start and end dates in your JavaScript code. Keep in mind that a JavaScript Date object represents a point in time (not a whole date), and that new Date() gives the point in time that is now.

Thus, you should probably create your start and end dates like this instead, so they are inclusive of the entire day rather than just the remainder of the day after the time ran the code.

var startDate = new Date();   // get the current "now"
startDate.setHours(0,0,0,0);  // adjust to the start of the local day
var endDate = new Date(startDate);      // copy to another instance
endDate.setDate(endDate.getDate()   7); // advance 7 days