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();
}
2
Answers
Simply put:
From Docs:https://www.postgresql.org/docs/current/datatype-datetime.html
The SQL standard differentiates timestamp without time zone and timestamp with time zone literals by the presence of a “+” or “-” symbol and time zone offset after the time. Hence, according to the standard,
TIMESTAMP ‘2004-10-19 10:23:54’
is a timestamp without time zone, while
TIMESTAMP ‘2004-10-19 10:23:54+02’
is a timestamp with time zone. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type:
TIMESTAMP WITH TIME ZONE ‘2004-10-19 10:23:54+02’
In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).
Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.
The error text suggests that
birthday
field data type is notdate
buttimestamp with time zone
, but it is not the case. Maketable_users.birthday
andUserInformation.Birthday
of typeDateOnly
(which corresponds to PostgreSQL’sdate
, available since .NET 6) rather thanDateTime
. This should solve the issue.