Tim Cross wrote: > Niels Jespersen <NJN@xxxxxx> writes: > > > Hello all > > > > We have some data that have entered a timestamp column from a csv. The data in the csv are in utc. We want to access the data in > > our native timezone (CET). > > > > I am considering a few alternatives: > > > > 1. Early in the process, convert to timestamptz and keep this datatype. > > > > 2. Early in the process, convert to timestamp as understood in CET. This will imply by convention that the data in the timestamp > > column represents CET. Users will need to be told that data represents CET, even if data is somwhere in the future kept in another > > country in another timezone. > > > > I probably should choose 1 over 2. But I am a bit hesitant, probably because we almost never have used timestamptz. > > > > Can we agree that the below query is selecting both the original utc timestamps and 2 and 1 (as decribed above)? > > > > set timezone to 'cet'; > > > > select read_time read_time_utc, (read_time at time zone 'utc')::timestamp read_time_cet, (read_time at time zone 'utc')::timestamptz > > read_time_tz from t limit 10; > > > > We are on Postgres 12. > > Keep your life simple - just go with option 1. Keep all timestamps in > UTC and let clients deal with it in whatever way they need to. That suggestion (keep timestamps in UTC) doesn't sound like option 1 to me (i.e. convert timestamps-that-are-in-UTC-but-don't-record-the-fact to timestamptz). Option 1 could be done using any timezone. The point is to record what the timezone is. It doesn't matter whether it's left as UTC or converted to CET as long as that decision is recorded in the data by using timestamptz (and as long as you get postgres to perform the time zone conversion because it will get it right). But presumably there is some reason why the data is already in UTC and there's probably no reason to change that. But its timezone should be recorded. > This will > also help deal with issues associated with daylight savings time (DST > can be a real pain as different locations have it and others don't and > the 'switchover' date is subject to political whims and can change). That's a dim view of the tzdata database which gets updated regularly to take such political decisions into account. As long as postgres uses the same tzdata as all good UNIX-based operating systems do, and it's kept up to date, it should be fine (as long as you never trust what a Windows host says the timezone is for arbitrary timestamps). This is required even if you leave data in UTC if it ever needs to be displayed in any other time zone. Postgres still needs up to date tzdata to perform the conversions later for users. > Your option 2 will cause all sorts of issues and keep in mind that most > larger countries have multiple timezones, so even if your data is all > associated with a single country, you can potentially have multiple > conversion routines required. On most *nix systems, clock time is UTC as > well, so having everything in UTC really helps when you want to do > diagnosis across your database and system log files etc. I don't see much difference in storing a timestamptz in UTC or a timestamptz in CET. As long as the intended offset from UTC is recorded (which it is in a timestamptz) it should be fine. If the timestamp is CET, then that fact is in the data and the user doesn't need to be told it separately. It's obvious when they see the data because the timezone is part of the data. And you don't need conversion routines. Postgres can compare timestamptz values and convert from one time zone to another for display purposes. However, option 2 seems to be converting the data to CET but not using the timestamptz datatype. That would be a bad idea. I think timestamptz should always be preferred to timestamp. Not recording the timezone is where the problems come from. Although having a single timezone for log files is a great idea. They hardly ever include timezone information so keeping everything in the same timezone is important. Mind you, they hardly ever even include the year. What's with that? (yes, I'm looking at you /var/log). > -- > Tim Cross cheers, raf
