[Idea] suggest developers to use timestamptz not timestamp type about strong_migrations HOT 13 CLOSED

I’m not disputing that timestamptz may be a better choice (especially if it’s recommend by Postgres), but I don’t think it’s so much better that developers should use it over the Rails convention. Locking this thread, as I don’t think there’s anything left to discuss here.

from strong_migrations.

Hey @bheemreddy181, thanks for the suggestion. I’m pretty familiar with both types, but don’t see why timestamptz would be recommended. Can you explain more?

from strong_migrations.

timestamptz records a single moment in time. Despite what the name says it doesn't store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC. You can insert values in any timezone and it'll store the point in time that value describes. By default it will display times in your current timezone, but you can use at time zone to display it in other time zones.

Because it stores a point in time it will do the right thing with arithmetic involving timestamps entered in different timezones - including between timestamps from the same location on different sides of a daylight savings time change.

timestamp (also known as timestamp without time zone) doesn't do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don't know what time it records. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter may give the wrong answer.

So if what you want to store is a point in time, rather than a picture of a clock, use timestamptz.

from strong_migrations.

And @ankane given if you have multiple products in a different timezone, like on a product in the UK and One in the US it would be difficult to migrate data from prod to snapshots or to any other scrubbed db we should always know which timezone they are from

from strong_migrations.

@ankane does any of this makes sense

from strong_migrations.

FYI reference https://it.toolbox.com/blogs/josh-berkus/zone-of-misunderstanding-092811

from strong_migrations.

Hey @bheemreddy181, thanks for the link. I don't agree with the analogy above, but don't think that's important. timestamptz does provide some niceties, like not truncating the time zone and simpler queries when doing time zone conversion, but I don't think it's a big enough difference to go against the Rails convention. I'd look into submitting a pull request for Rails if you'd like to get this changed.

from strong_migrations.

Sure @ankane 👍

from strong_migrations.

@ankane timestamp without time zone is officially recommended by the Postgres community as one of things to avoid, and has been listed for years on Postgres' Wiki entitled - Don't Do This: A Short List of Common Mistakes: https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

Supporting this, timestamp with time zone (timestamptz) conforms to https://en.wikipedia.org/wiki/ISO_8601, whereas timestamp without time zone does not. This standard notes that it is ambiguous in particular when communicating across time zones to not include time zone offset: "If no UTC relation information is given with a time representation, the time is assumed to be in local time. While it may be safe to assume local time when communicating in the same time zone, it is ambiguous when used in communicating across different time zones. Even within a single geographic time zone, some local times will be ambiguous if the region observes daylight saving time. It is usually preferable to indicate a time zone (zone designator) using the standard's notation."

Using timestamp without time zone can be disastrous in practice for a multi-time-zone company, because it has no objective sense. Also, it requires an additional level of operators in order to work with timestamp without time zone reliably. You first have to determine "at which time zone was this saved"? That question need not be asked ever if using timestamptz. So then you also have to account for which time zone you want to display it in. The result of this is that if I want to view a timestamp objectively, my query has to be field_name at time zone 'utc' at time zone 'America/Chicago', for example. I ought only need to consider the latter part.

from strong_migrations.

@jfinzel Rails converts all timestamps to UTC by default, so you don't need to determine what time zone it was saved in. I've successfully used timestamp in companies that were multi-time zone, so that argument is not sound. As I mentioned in my last comment, work with the Rails team if you'd like to see this changed.

from strong_migrations.

@ankane we simply can't assume Rails is our only platform, and make that a requirement to know what the data actually means. Also, nothing but convention prevents me from overriding the default behavior.

You don't address any of the very specific and good reasons that it is a standardized format to include offset information in a serialized timestamp. Just because it can be made to work doesn't mean it's a good solution for scale and minimizing technical debt. One person's success or failure with a platform doesn't answer all questions.

We will work with Rails team on the suggestion, but you should take note of the fact that the Rails standard is in conflict with the Postgres standard (and ISO_8601), even if you disagree.

from strong_migrations.

The standards argument is not a good one either, as timestamp follows the SQL standard, and timestamptz does not...

from strong_migrations.

@ankane one more reference just for a read https://www.depesz.com/2014/04/04/how-to-deal-with-timestamps/

from strong_migrations.