ASP.NET 4.6 MVC How to return a Json result including datetime data with correct timezone?

Question

I wish to return a Json Result containing the datetime queried from database. The testing on my local machine is without any problem, however, after publishing to production server, all datetime show 3 hours ahead. I assume it is due to the server located in another timezone area.

Need help to solve this issue.

Data in Database (MS SQL):

StartDt: 2019-07-02 04:00:00.000

Controller.cs:

 [HttpGet]
        public ActionResult GetAll()
        {
            CalendarModel calendarModel = new CalendarModel();

            var calendarEvents = from cal in db.Calendar
                                 orderby cal.created descending
                                 select cal;


            return Json(calendarEvents, JsonRequestBehavior.AllowGet);
        }

Json Result Fetched from my compuer:

[
    {
        //some data
        "startDt": "/Date(1562054400000)/",
        //some data
    },

The above datetime is parsed as "2019-07-02T04:00:00.000-04:00", which is correct.

Json Result Fetched from production server (queried from same database):

[
    {
        //some data
        "startDt": "/Date(1562065200000)/",
        //some data
    },

This datetime is "2019-07-02T07:00:00.000-04:00", which is wrong.

--------Update my solution-------

Thank @TommasoBertoni's answer inspired me that the key reason for this issue is due to the Unspecified DateTime Kind by default but turns to be local while Json serializing. So just need to set the DateTime Kind to UTC can solve that, but be aware that parsing the DateTime in front end also need to take it as UTC otherwise it will be considered as local by default.

Controller.cs:

 [HttpGet]
public ActionResult GetAll()
        {
            CalendarModel calendarModel = new CalendarModel();

            var calendarEvents = from cal in db.Calendar
                                 orderby cal.created descending
                                 select cal;
            //Add this
            foreach (var item in calendarEvents)
            {
                item.startDt = DateTime.SpecifyKind(item.startDt, DateTimeKind.Utc);
            }

            return Json(calendarEvents, JsonRequestBehavior.AllowGet);
        }

.js (using moment.js library)

//parse it as utc
  moment.utc(startDt).format("YYYY-MM-DDTHH:mm:ss")

Have you checked the timezone on production? This is a timezone mismatch — tech-y
– tech-y, Commented Jul 18, 2019 at 10:24
Which database is this, and how is it configured? What do you want the value in the database to mean, exactly? — Jon Skeet
– Jon Skeet, Commented Jul 18, 2019 at 10:26
How are you turning the Date into a date object and where are you getting your "date" (2019-07-02T07:00:00.000-04:00) from on the client side? That looks like a date representation for a UTC-4 timezone, so your assesment that 2019-07-02T04:00:00.000-04:00 "is correct" doesn't seem right — Liam
– Liam, Commented Jul 18, 2019 at 10:26
@tech-y I can't do any configuration on database. So I think the only way is to do something on server. — Lei.L
– Lei.L, Commented Jul 18, 2019 at 10:47

Tommaso Bertoni · Accepted Answer · 2019-07-18 15:06:13Z

The problem is that ASP.NET uses a custom Microsoft JSON date format, that encodes DateTime values as /Date(ticks)/, where ticks represents milliseconds since epoch (UTC).
So November 29, 1989, 4:55:30 AM, in UTC is encoded as /Date(628318530718)/ (see here for more).

Example:

Microsoft JSON date format: /Date(1563464520158)/
ISO 8601 format: 2019-07-18T15:42:02.4592008Z

If a DateTime has an Unspecified kind, it will be assumed as Local and the value will be conveted to UTC in order to get the ticks since epoch.

This json format is still used in MVC, but not in Web API: this means that when you are in a Controller and serialize the result with Json(...) you'll get the non-ISO format, but if you're in an ApiController the default serializer is Json.NET, which supports the ISO 8601 format and won't convert the DateTime value.

So, to fix this behavior either you switch to Web APIs, or if you want to keep using the MVC controller, you can see the answers to these questions:

...or you could force the DateTime Kind to be Utc right before the json serialization, but I wouldn't recommend that.

class TestRepo
{
    public IEnumerable<DateTime> GetDates()
    {
        var now = DateTime.Now;

        // Use DateTime instances with different Kind
        // showing that it doesn't impact the serialization format.
        var utc = DateTime.SpecifyKind(new DateTime(now.Ticks), DateTimeKind.Utc);
        var local = DateTime.SpecifyKind(new DateTime(now.Ticks), DateTimeKind.Local);
        var unspecified = DateTime.SpecifyKind(new DateTime(now.Ticks), DateTimeKind.Unspecified);

        return new DateTime[] { utc, local, unspecified };
    }
}

// MVC controller
public class MVCValuesController : Controller
{
    public ActionResult Index()
    {
        IEnumerable<DateTime> dates = new TestRepo().GetDates();
        return Json(dates, JsonRequestBehavior.AllowGet);
    }
}

// json result:
[
    "/Date(1563465361835)/", // <-- Utc
    "/Date(1563458161835)/", // <-- Local
    "/Date(1563458161835)/"  // <-- Unspecified
]

// Web API controller
public class ValuesController : ApiController
{
    public IEnumerable<DateTime> Get()
    {
        IEnumerable<DateTime> dates = new TestRepo().GetDates();
        return dates;
    }
}

// json result:
[
    "2019-07-18T15:56:03.6401158Z",      // <-- Utc
    "2019-07-18T15:56:03.6401158+02:00", // <-- Local
    "2019-07-18T15:56:03.6401158"        // <-- Unspecified
]

Appreciate your perfect explanation inspiring me solved my problem!

Collectives™ on Stack Overflow

ASP.NET 4.6 MVC How to return a Json result including datetime data with correct timezone?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related