How to set datestyle in postgres db in c#

Question

I want to change the datetype to th "DMY" Format in a postgres sql database. I use the Npgsql library as follows:

    public void SetDateSytleDB()
    {
        string sqlConnectionString = "Server=localhost;Port=5432;User Id=test;Password=test;Database=test_db";

        sqlCnctn = new NpgsqlConnection(sqlConnectionString);
        sqlCnctn.Open();
        if (sqlCnctn.State == ConnectionState.Open)
        {
            String strSetDatestyle = "SET datestyle = 'ISO, DMY'";

            using (var cmd = new NpgsqlCommand(strSetDatestyle, sqlCnctn))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }

unfortunately the datetype will not be changed in the database afterwards. When I call show datestyle, the value remains at "MDY". In PgAdmin this works like this:

SET datestyle = 'ISO, DMY';

How can I achieve this with Npgsql in C#?

The SQL Create Statements for my table

CREATE TABLE public.t_plate_history (
id integer NOT NULL,
plate integer NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
direction boolean NOT NULL,
"position" integer NOT NULL
);

The Timestamp is created in a sorted list with the DateTime.Now command.

SortedList<string, object> values = new SortedList<string, object>
                                    {
                                      {TblPlateHistory.FieldDirection, insert},
                                      {TblPlateHistory.FieldPlate, LocalDataSet.Plate.Id},
                                      {TblPlateHistory.FieldPosition, LocalDataSet.PlatePosition.Id},
                                      {TblPlateHistory.FieldTimestamp, DateTime.Now}
                                    };


{TblPlateHistory.FieldTimestamp, DateTime.Now}

The access via Npgsql then looks like this, the INSERT statement given in string sqlCmd is the following:

 INSERT INTO t_plate_history(plate,timestamp,direction,position) VALUES(1359,'09.02.2018 15:02:08' ,true,705)

An finally the code where i make the transaction with Npgsql:

public bool RunNonQueryCommand(string sqlCmd, NpgsqlTransaction transAction, NpgsqlConnection connection)
{
    NpgsqlCommand sqlCom = new NpgsqlCommand(sqlCmd, connection);
    bool rt = true;
    try
    {               
        sqlCom.Transaction = transAction;
        sqlCom.ExecuteNonQuery();
    }

    ...

    return rt;


}

Answer 1

If you have access to the server you can configure it in postgresql.conf.

Otherwise try setting the PGDATESTYLE environment variable on server or client as stated in the documentation.

Your way of setting it might work for the current transaction only. You can try to reuse the same open connection and check if it has effect.

The real question is why do you want to change this setting? Instead you can use insert statements like this:

INSERT INTO t_plate_history(id, plate,timestamp,direction,position) 
VALUES(1, 1359, timestamp '2018-02-09 15:02:08' ,true,705)

To get the this date format you can use:

DateTime dateTime = new DateTime(2018, 2, 9, 15, 2, 8); // get you datetime object
dateTime.ToString("yyyy-MM-dd HH:mm:ss")

Even better would be to use prepared statements, where you only set the DateTime object and the provider takes care of the rest. Check out the documentation