←back to thread

189 points GordonS | 2 comments | | HN request time: 0s | source
Show context
zeroimpl ◴[] No.19533112[source]
I sometimes wish there was an actual "TIMESTAMP WITH TIME ZONE" type that stored the input timestamp as well. Basically, a version that didn't canonicalize the input by converting to UTC. This way, I can easily show timestamps to the user in the time zone they used to input them.
replies(6): >>19533287 #>>19534038 #>>19534057 #>>19534316 #>>19535066 #>>19541872 #
welder ◴[] No.19534038[source]
Edit: I'm being downvoted, but I know more than you about timestamps. Check my bio before downvoting.

You want a column that can store both timestamp and timezone, so why not just have 2 columns? You already have to convert the timestamp into the user's timezone when rendering because the user's timezone can change. Storing timestamps with timezone just complicates things, for ex:

display_in_user_zone(TIMESTAMP UTC)

display_in_user_zone(TIMESTAMP_WITH_TIMEZONE)

The above is exactly the same, but the UTC one will prevent bugs in the long run.

The only time it's acceptable to store time with timezone is with DATES that do not have a TIME component.

If you really need to display the timestamp in the timezone the user originally entered, store the original timezone along with every timestamp.

replies(3): >>19534517 #>>19534530 #>>19535511 #
crote ◴[] No.19534530[source]
And your last sentence is exactly what many people want.

Let's say I'm writing a calendar application. Naturally, the user wants to enter all events in their own timezone, which in my case is Europe/Amsterdam. So I can just convert that to UTC, and store it along with a timezone string? Well, that's going to break horribly when the EU abolishes summer time next year, because all of those UTC stamps will be wrong!

The only sane option here is to store local time, and a string representing the timezone, which is what the name "timestamp with timezone" suggests it is. Yes, I could do the conversion manually, but it would be a lot more convenient to have it in the database directly.

replies(1): >>19535132 #
1. welder ◴[] No.19535132{3}[source]
Yes, you're right. Scheduling is one reason to store timestamps with timezone, to prevent information loss.
replies(1): >>19535989 #
2. zeroimpl ◴[] No.19535989[source]
This is exactly why I said in my original post that I sometimes want this - in many cases I don't, but whenever a user has explicitly input the data I'd like to easily be able to return it in the same timezone. I can (and do) store two columns in the database, but I'd prefer the database and JDBC interface natively support this as a single data type.

Right now, supposing you have two columns, EventTime TIMESTAMPTZ and EventTimeZone TEXT, you'd have to do something like the following in your query to get the data returned to the application properly:

    SELECT (EventTime AT TIME ZONE EventTimeZone) || ' ' || EventTimeZone