3

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?

3
+50

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:

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';
  • I guess I'm just not seeing the advantage of timestamptz if all clients expect UTC. To me it's a little like saying you should use a mythical type which stores all text in French, but translates it to and from the current users language (perfectly) on input/output. This type has more features, but the end result is the same if all clients are English - so why use the more complicated type? Am I just blinded by being in the UK and UTC is usually the right time for me? Would I have a different opinion if I was in a different timezone? – berkeleybross Apr 23 '18 at 21:28
  • Because like language itself, people won't speak "just Latin" for ever. And it's better to write code that doesn't make assumptions about the client. I would rather not have implicit assumptions if not needed at all. – Evan Carroll Apr 24 '18 at 0:02
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.