postgresqldate

Persisting Date Only in PostgreSQL Database


I am encountering an issue while persisting data in a PostgreSQL database where I have a birthday field of type date. The problem arises when I attempt to save data as it throws an error indicating that it only accepts "UTC" time:

Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported

However, when I try to save with UTC, it changes the birthday information. For instance, attempting to save 01/01/1980 results in 31/12/1979 being stored.

One workaround I have considered is using either timestamptz or storing the birthday information as a string, but I would prefer to persist only the date. Please note that birthday is not suitable for timestamp change.

Is there a way to save only the date without encountering this issue?

Any insights or suggestions would be greatly appreciated.


Postgres DDL:

-- Dumped from database version 15.3
-- Dumped by pg_dump version 15.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;

CREATE TABLE public.table_users(
    last_modified timestamptz,
    birthday date,
    //Other properties
);

Table setting at .Net Core:

public class table_users
{
    public DateTime last_modified { get; set; }
    public DateTime birthday { get; set; }
    //Other properties
}

Class setting at .Net Core:

public class UserInformation
{
    public DateTime LastModified { get; set; }
    public DateTime Birthday { get; set; }
    //Other properties
}

Action at .Net Core:

    public static void AddNewUser(UserInformation input)
    {
        var options = new DbContextOptionsBuilder<FlowEntities>().UseNpgsql( ... ).Options;
        using var database = new FlowEntities(options);
        var newUser = new table_users
        {
            last_modified = input.LastModified,
            birthday = input.Birthday,
            //Other properties
        };

        database.table_users.Add(newUser);
        database.SaveChanges();
    }

Solution

  • The error text suggests that birthday field data type is not date but timestamp with time zone, but it is not the case. Make table_users.birthday and UserInformation.Birthday of type DateOnly (which corresponds to PostgreSQL's date, available since .NET 6) rather than DateTime. This should solve the issue.