0

I am developing a WebApp which is going to be deployed on Azure. Because it has to be server indipendent, I think I have to store the DateTime as UTC, so my Postgres column is configured as timestamp without time zone and when I save a date I put the utc value, let say e.g 2021-10-14 06:16:33.813473. In this case the value returned to the client is the same since the Kind value server side is Unspecified and by parsing it using Date or dayjs and formatting it for the rendering, I get 14.10.2021 06:16 since I am at GMT+2, which is wrong, the desired output is 14.10.2021 08:16.

To fix it I could simply add the Z when parsing the date, as new Date("2021-10-14 06:16:33.813473" + "Z") but I think it's not a clean solution.

I could define the column as timestamp with time zone, suppose we have the same value in the db, but with time zone information: 2021-10-14 06:16:33.813473+00, to the client it is returned as 2021-10-14T08:16:33.813473+02:00 and on formatting it as previously I get the desired result. But in this case I have the problem saving the timestamps. If I save DateTime.UtcNow in the database I find the current time - 4 hours (instead of 2).

Another possible solution would be still have the column defined as timestamp without time zone as in the first example, having the date 2021-10-14 06:16:33.813473 and adding a JsonSerializerOption:

public class DateTimeConverter : JsonConverter<DateTime>
{
    public override DateTime Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
    {
        return DateTime.Parse(reader.GetString());
    }

    public override void Write(Utf8JsonWriter writer, DateTime value, JsonSerializerOptions options)
    {
        string s = value.ToString("O");
        if (value.Kind == DateTimeKind.Unspecified)
        {
            s += "Z";
        }
        writer.WriteStringValue(s);
    }
}

It works as expected, but IF I have another column defined as date, this also gets the Z and on the client the parsed value has the 2 hourses added.

How sould I do?
What problems could I encounter IF I use timestamp with time zone columns and set DateTime.Now instead DateTime.UtcNow?

PS. I am working with EF Core 5 and Microsoft.AspNetCore.Mvc.Core 5

Emaborsa
  • 2,360
  • 4
  • 28
  • 50
  • Are you storing times in the past or the future? I ask because (particularly) storing future times in UTC might not be the [problem resolving silver bullet](https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/) you hope it will be – Caius Jard Oct 14 '21 at 07:15
  • Currently I am dealing with `created_at` and `modified_at` – Emaborsa Oct 14 '21 at 07:17
  • Not sure if it answers your question, but you can use static method `DateTime.SpecifyKind(...)` to overwrite the `Kind` with another one, or `dateTimeValue.ToUniversalTime()` to try to convert a time to universal time based on the OS's idea of the local timezone (if the kind does not seem to show that it is already a universal time, otherwise this method returns the value unchanged). Be aware that with format string `"O"`, the `Kind` ***is*** encoded in the string. – Jeppe Stig Nielsen Oct 14 '21 at 07:23
  • Im having a hard time understanding your particular problem. Im not sure im understanding the problem correctly but if your problem is that the utc is not converted to the correct timezone. What you should do is save it as UTC, and then apply the conversion whenever you need it. You can get that timezone with a header from the browser from example – nalnpir Oct 14 '21 at 07:24
  • 1
    As I understand the you set date on client side (javascript?) then send it in json format to server side where you convert it to DateTime and set in into an EF object's property which finally gets saved into Azure SQL table's column declared as timestamp without time zone, right? This means 3 machine tz settings (client, app, sql) may be involved and 3 runtime environment's (javascript, c#, sql) datetime handling methods should be used appropriatelly. Please add more related code (at least js side) – cly Oct 14 '21 at 07:25
  • @JeppeStigNielsen Well it is more or less what I am doing in the converter. Using `SpecifyKind` to put `UTC` is a nicer way to add the `Z`. However, I had the same problem with values of `date` types columns. – Emaborsa Oct 14 '21 at 07:31
  • @nalnpir I actually am saving them as `UTC` using C# `DateTime.UtcNow`, setting directly the value to the `EF ` objects field. So your idea is to send to the client the UTC string without timezone information and add the offset offset on client side? – Emaborsa Oct 14 '21 at 07:34
  • @cly No, actually I never mentioned to send the value using `JSON` from the client to the server, neither using `Azure SQL`. The WebApp could be hosted everywhere, the `Postgres` database as well. The `DateTime.UtcNow` is set directly in `C#` code. – Emaborsa Oct 14 '21 at 07:38
  • What is the purpose of JsonConverter code? – cly Oct 14 '21 at 07:44
  • Does this answer your question? [DateTime vs DateTimeOffset](https://stackoverflow.com/questions/4331189/datetime-vs-datetimeoffset) – jazb Oct 14 '21 at 07:53
  • @Jazb Well, I'm not sure about it `DateTimeOffset is not a UTC time. It's a local time, plus an offset. It's not a timezone, because it does not take daylight savings variations into account`. So i fear it is not the right solution. Taken here: https://github.com/npgsql/efcore.pg/issues/473#issuecomment-405031077 – Emaborsa Oct 14 '21 at 08:05
  • @cly I must confess I'm not sure about it. On the web I see many examples where the returned string from an API ends with `Z` and I found an example which suggested to add a serializer in order to customize the output. – Emaborsa Oct 14 '21 at 08:12
  • So there is a code line where an EF entity's property set like ````entity.created_at = DateTime.UtcNow```` then you save it into a Postgres db. Is the value correct in the db? – cly Oct 14 '21 at 09:22
  • That's a good question, since a really don't know if I have to use `timestamp with time zone` or `timestamp without time zone`. – Emaborsa Oct 14 '21 at 09:35
  • @Emaborsa No my idea, is whenever you have to send to the client the time in his timezone, that you build that with either information of the client (previously saved) or on the fly with a header (assuming you are using HTTP requests). Then you have some libraries to handle it, such as Noda time. From my point of view you should not delegate the timezone convertion to the frontend but I ve seen it in several projects so its not bad either – nalnpir Oct 14 '21 at 16:58

0 Answers0