PostgreSQL – Is Timestamptz Preferred for Client Timezone Logic?

datetimepostgresqltimestamp

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 datetime2 with.

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 Instant to 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.

TLDR: Is timestamptz still preferred if the application always inserts/reads UTC and converts to local datetime itself?

Best Answer

Is timestamptz still preferred if the application always inserts/reads UTC and converts to local datetime itself?

Well, no. Then it's very slightly simpler / faster to use timestamp.

But is "the application" the only client accessing the database? And is it going to stay this way for the lifetime of the DB?

If doubts remain, I would still use timestamptz, it's the safe choice. You already found my related answer on SO I would suggest as reference for basic information:

Also, timestamptz literally is the "preferred" type among "Date/time types" in Postgres (tagged typeispreferred in pg_type), which can make a difference, but probably not to your particular question, even though the question title almost sounds like you might be asking for that. Related:

Related Question