CODEWITHSUNDEEP
.netpostgresql.net-coreutc
Herman Stashinskii
Herman Stashinskii

Reputation: 405

How to deal with TIMEZONE in PostgreSQL's timestamptz

I have Users table with birthdate column of timestamptz type.
TIMEZONE in PostgreSQL is set to UTC.
I am setting birthdate in following way in code (.NET Core):

user.birthDate = DateTime.UtcNow;

What would happen if I would set TIMEZONE to America\Los Angeles and save my date (UTC representation from .NET code)? My UTC representation will be considered as America\Los Angeles and it will lead to invalid data representation?

Should I always keep TIMEZONE is set to UTC? Is it best practice for timestamptz?

Upvotes: 0

Views: 400

Answers (1)

pifor
pifor

Reputation: 7892

https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-TIMEZONES says

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

In your client code, you can change time zone parameter at session level; with timestamptz (timestamp with time zone) at database server level, there is nothing to change.

It is considered best pratice to use timestamp with timezone instead of timestamp without time zone: see https://tapoueh.org/blog/2018/04/postgresql-data-types-date-timestamp-and-time-zones/#date-time-and-time-zonesenter link description here

Upvotes: 1

Related Questions