C#- "DateTime.UtcNow" outputs date in a format which is treated as out of range for Postgr-CodePudding

I have shifted to a new laptop (windows 10 i5) and trying to set up my .NET app. I am encountering a weird problem and it has taken a whole lot of my time. In my app, I am storing the current date-time in UTC format in a Postgres DB table. The c# command DateTime.UtcNow outputs the date format as

14-12-2021 13:50:57

When the above date is passed via an insert query to be stored in the Postgres Db, it throws the error as

ERROR: date/time field value out of range: "14-12-2021 13:50:57"

What seems even stranger is, that on my old laptop (same OS - windows 10 i3), the same code works fine and gives the output of the date as

12/14/2021 07:24:24 AM

and this format is accepted by the Postgres DB.

The table design is as follows

CREATE TABLE IF NOT EXISTS public."Log"
(
    "Id" bigint NOT NULL DEFAULT nextval('"Log_Id_seq"'::regclass),
    "QueueId" uuid NOT NULL,
    "MessageId" character varying(100) COLLATE pg_catalog."default",
    "LogTime" timestamp without time zone,   
   
    CONSTRAINT "Log_pkey" PRIMARY KEY ("Id")
)

The Value resulting from the DateTime.UtcNow is being passed into the LogTime column.

Here is the, c# code that forms the Postgres query.

string query = $@"INSERT INTO public.""Log""(
                            ""QueueId"", 
                            ""LogTime"", 
                            ""Message"", 
                            )
                            VALUES(
                            '{req.QueueId}', 
                            '{req.LogTime}', 
                            '{(item.Message.Length > 100 ? item.Message.Substring(0, 100) : item.Message)}'                           
                            RETURNING ""Id"";";

The same code behaves differently on different machines with the same OS. Any clues would be extremely appreciated. Thanks for any help.

CodePudding user response：

I repeat my comment above because you are transforming a datetime in a string when you write '{req.LogTime}', and with that the code creates a parsing problem for your database because it needs to translate that string back to a timespan to insert it in the column. Not only this is prone to different result when converting the strings (one pc thinks that the right format for a date is "MM-dd", the other one thinks of "dd-MM") but it is also creates a well known vulnerability called Sql Injection.
You should simply use parameters to pass values to your database.
With a proper type defined parameter there is no more ambiguity in the resolution of the value and the database is not affected by script kids trying the easiest hack in the world.

string query = $@"INSERT INTO public.""Log""(
                  ""QueueId"", LogTime"", ""Message"")
                  VALUES(@qid, @ltime, @msg)                           
                  RETURNING ""Id"";";

using (var cmd = new NpgsqlCommand(query, conn))
{
    cmd.Parameters.Add("@qid", NpgsqlDbType.Uuid).Value = req.QueueId;
    cmd.Parameters.Add("@ltime", NpgsqlDbType.Timestamp).Value = req.LogTime;
    cmd.Parameters.Add("@msg", NpgsqlDbType.Varchar).Value = 
         (item.Message.Length > 100 ? item.Message.Substring(0, 100) : item.Message);
   var id = cmd.ExecuteScalar();
}

CodePudding user response：

Set the datestyle to match your input. This one works for me:

SET DateStyle to DMY;
SELECT '14-12-2021 13:50:57'::TIMESTAMP