I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace
The general advice seems to be always use
timestamptz, and never use
timestamp. The reasons given tend to be along the lines that timestamp and
timestamptz are stored the same regardless (so no performance penalty) and
timestamptz auto-converts to the timezone of the connection. Ignoring timezones altogether in Rails and PostgreSQL | Stack Overflow
Unfortunately my legacy .NET codebase is very inconsistent with datetimes and we usually render in UTC regardless of the users timezone. More recent code has been using NodaTime and it's
Instant class, but we rarely have to deal with times and displaying just date has been "close enough". My understanding of using NodaTime properly, however, is to convert an
LocalDateTime as late as possible - and not in the database.
In addition to this, I'm not entirely sure how Postgres knows the correct timezone of the "current user". I know you can set the timezone specifically as a session parameter
SET TIME ZONE 'UTC';, are you expected to do this for every connection as appropriate for the "current user"? If so, is this reset whenever the connection is retrieved from the connection pool? I also see that Npgsql has the ability to set a timezone for a connection string, presumably this isn't appropriate if it's per user?
All this leads me to think the best option is to use
timestamp for all datetimes, and use application logic to convert to local datetime. I guess another option is to use
timestamptz for all datetimes, force the connection to use UTC in the connection string, and use application logic to convert to local datetime. However I worry that Postgres will perform extra work in doing a no-op conversion between UTC and UTC.
timestamptz still preferred if the application always inserts/reads UTC and converts to local datetime itself?