Is timestamptz preferred when timezone logic is performed by the client?

Question

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?

Answer 1

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:

• Ignoring time zones altogether in Rails and PostgreSQL

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:

• Generating time series between two dates in PostgreSQL

Answer 2

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

YES: timestamp should always be avoided unless you're calculating something abstract -- like dateranges. And, only then because PostgreSQL doesn't have a date type. It's literally the same size. Worrying about the speed difference is a pre-optimization if I've ever heard of one.

Your concerns,

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?

No for your use-case you can just set the timezone configuration parameter in postgresql.conf. From the docs,

The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 19.

Adding the line timezone = 'UTC' to your postgresql.conf should solve all of your problems, unless you're

• Sending a spurious time zone with your .NET app.
• Overriding the time zone for the session

Here are some examples,

-- Sets the time-zone for the session
SET TimeZone = 'UTC';

-- Assumes that the time is already in UTC.
SELECT timestamp with time zone '2018-04-21 23:51:11.193009';
-- Assumes that the time is in USA/CST (notice the -05)
SELECT timestamp with time zone '2018-04-21 23:51:11.193009-05';

Further, while TimeZones will be default-presented in UTC (after setting the configuration variable). They needn't be that way in the future, just manually set the time zone for the session as we did above, or request PostgreSQL to override it with AT TIME ZONE

-- Sets the time-zone for the session
SET TimeZone = 'UTC';

-- Interprets this time as UTC, and returns it as UTC.
SELECT timestamp with time zone '2018-04-21 23:51:11.193009';

-- Returns the time in USA/CST
SELECT timestamp with time zone '2018-04-21 23:51:11.193009' AT TIME ZONE 'CST5CDT';

Answer 3

So after a few issues with timezone's auto-converting incorrectly, I've decided to use timestamp and NOT timestamptz. Whilst it maybe best practice from Postgres point of view, it seems the library I'm using, Npgsql, suggests differently.

https://github.com/npgsql/npgsql/issues/546

https://github.com/npgsql/npgsql/issues/1469