![]() ![]() Hours later in Asia/Kolkata, still later in Europe/Paris, still more hours later in America/Montreal and so on.Įach of these specific delivery moments would be recorded by the elves using WITH TIME ZONE, while that general idea of Christmas would by stored as WITHOUT TIME ZONE.Īnother use in business apps for WITHOUT TIME ZONE is scheduling appointments farther out than several weeks. They continue going westward as each zone’s midnight arrives. The elves schedule a flight plan taking the reindeer on to other time zones where midnight comes shortly after, such as Pacific/Auckland. ![]() The elves schedule Santa’s first arrival there. The earliest time zone is currently Pacific/Kiribati, 14 hours ahead of UTC. The elves staffing Santa’s Special Events Logistics Department apply the time zones as part of their planning process. So we store this using TIMESTAMP WITHOUT TIME ZONE. It has no meaning until we apply a time zone (or offset). This value is only a vague idea about possible moments. That would be TĠ0:00:00 with no indicator of time zone nor offset-from-UTC. If you want to store the general idea of a possible time rather than a specific moment, use the other type, TIMESTAMP WITHOUT TIME ZONE.įor example, Christmas starts this year at the first moment of December 25th, 2017. Read the doc carefully, and experiment a bit to clarify your understanding. But again, moot as you should almost never use this type. But without any time frame reference, anyone looking at that data would have to assume (hope, pray?) that the values are UTC. More precisely, one should say that TIMESTAMP WITHOUT TIME ZONE stores date-time values with no time zone. (This behavior is described for Postgres - Other databases vary widely in this regard.) The difference between the types is whether any time zone should be applied either during storage (INSERT or UPDATE) or retrieval (SELECT query). The "with time zone" phrase means "with attention paid to time zone", not "store the time zone alongside this value". In both cases the date-time value is stored in UTC (no time zone offset). The data types names timestamp with time zone and timestamp without time zone are misnomers. A rare exception for most of us.įor information about partitioning, see doc and see the Wiki. Wheeler’s sole exception is when partitioning on timestamps, because of technical limitations. If you are tracking actual moments, specific points on the timeline, use TIMESTAMP WITH TIME ZONE. Wheeler, a Postgres expert, in a blog post whose title says it all:Īlways Use TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) If you use other applications to also connect to the same database, timestamptz will have different implications.Here's advice from David E. If you’re using just Elixir and your application is the only one connecting to the database, then it doesn’t make a difference if you use timestamp or timestamptz. In your schema you would use something like timestamps(type: :utc_datetime) and in migration timestamps(type: :timestamptz). It’s easy to override this in your migrations. Personally I don’t find much use in timestamptz, but that’s up to everyone to decide for themselves. It does not even store the offset/timezone. There is the confusingly named timestamp with timezone but all it does is convert your input timestamp to UTC and convert it back to whatever your DB connection’s timezone is when reading. This is because PostgreSQL does not have a data type for storing a timestamp with timezone. Back when Ecto was created, Elixir didn’t even have datetime types and Ecto didn’t have timezone support.Īlso quoting my earlier answer in an earlier topic:īTW, if you need to store the timezone too (in PostgreSQL), you have no option but to use a naive datetime field and store the offset or timezone information separately. The reason why Ecto’s timestamps() are naive by default is backwards compatibility. Please use the search facilities of this forum, just a search for timestamptz returns several topics that have details about this. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |