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();
}
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.