Thread: Support for DATETIMEOFFSET
I've noticed that Postgres doesn't have support for DATETIMEOFFSET (or any functional equivalent data type) yet. Is this on the roadmap to implement? I find it a very useful data type that I use all over the place in TSQL databases. -- Best regards, Jeremy Morton (Jez)
On 4/10/20 10:34 AM, Jeremy Morton wrote: > I've noticed that Postgres doesn't have support for DATETIMEOFFSET (or > any functional equivalent data type) yet. Is this on the roadmap to > implement? I find it a very useful data type that I use all over the > place in TSQL databases. Hi, I do not think anyone is working on such a type. And personally I think such a type is better suite for an extension rather than for core PostgreSQL. For most applications the timestamptz and date types are enough to solve everything time related (with some use of the timestamp type when doing calculations), but there are niche applications where other temporal types can be very useful, but I personally do not think those are common enough for inclusion in core PostgreSQL. I suggest writing an extension with this type and see if there is any interest in it. Andreas
Oh well. Guess I keep using SQL Server then. datetimeoffset makes it impossible for developers to make the mistake of forgetting to use UTC instead of local datetime, and for that reason alone it makes it invaluable in my opinion. It should be used universally instead of datetime. -- Best regards, Jeremy Morton (Jez) Andreas Karlsson wrote: > On 4/10/20 10:34 AM, Jeremy Morton wrote: >> I've noticed that Postgres doesn't have support for DATETIMEOFFSET >> (or any functional equivalent data type) yet. Is this on the >> roadmap to implement? I find it a very useful data type that I use >> all over the place in TSQL databases. > > Hi, > > I do not think anyone is working on such a type. And personally I > think such a type is better suite for an extension rather than for > core PostgreSQL. For most applications the timestamptz and date types > are enough to solve everything time related (with some use of the > timestamp type when doing calculations), but there are niche > applications where other temporal types can be very useful, but I > personally do not think those are common enough for inclusion in core > PostgreSQL. > > I suggest writing an extension with this type and see if there is any > interest in it. > > Andreas > > >
Jeremy Morton <admin@game-point.net> writes:
> Oh well. Guess I keep using SQL Server then. datetimeoffset makes it
> impossible for developers to make the mistake of forgetting to use UTC
> instead of local datetime,
Really? That would be a remarkable feat for a mere datatype to
accomplish.
> and for that reason alone it makes it
> invaluable in my opinion. It should be used universally instead of
> datetime.
What's it do that timestamptz together with setting timezone to UTC
doesn't?
regards, tom lane
> On Apr 10, 2020, at 8:19 AM, Jeremy Morton <admin@game-point.net> wrote: > > Oh well. Guess I keep using SQL Server then. datetimeoffset makes it impossible for developers to make the mistake offorgetting to use UTC instead of local datetime, and for that reason alone it makes it invaluable in my opinion. It shouldbe used universally instead of datetime. 1. Not sure I understand. I’ve never used datetimeoffset so please bear with me. How does storing a time zone with the datetime “make it impossible for developers to make the mistake….” 2. I usually work with timestamps that have input and output across multiple time zones, why would one store a time zonein the database? If I need a local time, then postgres does that automatically. 3. At the end of the day a point in time in UTC is about as clear as it is possible to make it. Not trying to be difficult, just trying to understand. Neil > > -- > Best regards, > Jeremy Morton (Jez) > > Andreas Karlsson wrote: >> On 4/10/20 10:34 AM, Jeremy Morton wrote: >>> I've noticed that Postgres doesn't have support for DATETIMEOFFSET (or any functional equivalent data type) yet. Isthis on the roadmap to implement? I find it a very useful data type that I use all over the place in TSQL databases. >> Hi, >> I do not think anyone is working on such a type. And personally I think such a type is better suite for an extensionrather than for core PostgreSQL. For most applications the timestamptz and date types are enough to solve everythingtime related (with some use of the timestamp type when doing calculations), but there are niche applications whereother temporal types can be very useful, but I personally do not think those are common enough for inclusion in corePostgreSQL. >> I suggest writing an extension with this type and see if there is any interest in it. >> Andreas > >
On 4/10/20 3:19 PM, Jeremy Morton wrote: > Oh well. Guess I keep using SQL Server then. datetimeoffset makes it > impossible for developers to make the mistake of forgetting to use UTC > instead of local datetime, and for that reason alone it makes it > invaluable in my opinion. It should be used universally instead of > datetime. I think that the timestamptz type already helps out a lot with that since it accepts input strings with a time zone offest (e.g. '2020-04-10 17:19:39+02') and converts it to UTC after parsing the timestamp. In fact I would argue that it does so with fewer pitfalls than the datetimeoffset type since with timestamptz everything you read will have the same time zone while when you read a datetimeoffset column you will get the time zone used by the application which inserted it originally, and if e.g. one of the application servers have a different time zone (let's say the sysadmin forgot to set it to UTC and it runs in local time) you will get a mix which will make bugs hard to spot. I am not saying there isn't a use case for something like datetimeoffset, I think that there is. For example in some kind of calendar or scheduling application. But as a generic type for storing points in time we already have timestamptz which is easy to use and handles most of the common use cases, e.g. storing when an event happened. Andreas
Neil wrote: > >> On Apr 10, 2020, at 8:19 AM, Jeremy Morton <admin@game-point.net> wrote: >> >> Oh well. Guess I keep using SQL Server then. datetimeoffset makes it impossible for developers to make the mistake offorgetting to use UTC instead of local datetime, and for that reason alone it makes it invaluable in my opinion. It shouldbe used universally instead of datetime. > > 1. Not sure I understand. I’ve never used datetimeoffset so please bear with me. How does storing a time zone with thedate time “make it impossible for developers to make the mistake….” At just about every development shop I've worked for, I've seen developers use methods to get a local DateTime - both in the DB and in the code - such as DateTime.Now, and throw it at a DateTime field. Heck, even I've occasionally forgotten to use .UtcNow. With DateTimeOffset.Now, you can't go wrong. You get the UTC time, and the offset. I've taken to using it 100% of the time. It's just really handy. -- Best regards, Jeremy Morton (Jez)
> On Apr 10, 2020, at 6:10 PM, Jeremy Morton <admin@game-point.net> wrote: > > Neil wrote: >>> On Apr 10, 2020, at 8:19 AM, Jeremy Morton <admin@game-point.net> wrote: >>> >>> Oh well. Guess I keep using SQL Server then. datetimeoffset makes it impossible for developers to make the mistakeof forgetting to use UTC instead of local datetime, and for that reason alone it makes it invaluable in my opinion. It should be used universally instead of datetime. >> 1. Not sure I understand. I’ve never used datetimeoffset so please bear with me. How does storing a time zone with thedate time “make it impossible for developers to make the mistake….” > > At just about every development shop I've worked for, I've seen developers use methods to get a local DateTime - both inthe DB and in the code - such as DateTime.Now, and throw it at a DateTime field. Heck, even I've occasionally forgottento use .UtcNow. With DateTimeOffset.Now, you can't go wrong. You get the UTC time, and the offset. I've takento using it 100% of the time. It’s just really handy. > In PostgreSQL there are two types; timestamp and timestamptz. If you use timestamptz then all time stamps coming into thedatabase with time zones will be converted to and stored in UTC in the database and all times coming out of the databasewill have the local time zone of the server unless otherwise requested. Not sure how that is error prone. Maybe you are working around a problem that does not exist in PostgreSQL. If you use timestamp type (not timestamptz) then all input output time zone conversions are ignored (time zone is truncated)and sure problems can occur. That is why there is very little use of the timestamp type. Neil https:://www.fairwindsoft.com
Jeremy Morton <admin@game-point.net> writes:
> At just about every development shop I've worked for, I've seen
> developers use methods to get a local DateTime - both in the DB and in
> the code - such as DateTime.Now, and throw it at a DateTime field.
> Heck, even I've occasionally forgotten to use .UtcNow. With
> DateTimeOffset.Now, you can't go wrong. You get the UTC time, and the
> offset. I've taken to using it 100% of the time. It's just really handy.
It sounds like what you are describing is a client-side problem, not
a server issue. If you have such a thing in the client code, why
can't it readily be mapped to timestamptz storage in the server?
regards, tom lane
Andreas Karlsson wrote: > On 4/10/20 3:19 PM, Jeremy Morton wrote: >> Oh well. Guess I keep using SQL Server then. datetimeoffset makes >> it impossible for developers to make the mistake of forgetting to >> use UTC instead of local datetime, and for that reason alone it >> makes it invaluable in my opinion. It should be used universally >> instead of datetime. > > I think that the timestamptz type already helps out a lot with that > since it accepts input strings with a time zone offest (e.g. > '2020-04-10 17:19:39+02') and converts it to UTC after parsing the > timestamp. In fact I would argue that it does so with fewer pitfalls > than the datetimeoffset type since with timestamptz everything you > read will have the same time zone while when you read a datetimeoffset > column you will get the time zone used by the application which > inserted it originally, and if e.g. one of the application servers > have a different time zone (let's say the sysadmin forgot to set it to > UTC and it runs in local time) you will get a mix which will make bugs > hard to spot. I don't understand how that makes bugs hard to spot. And if the "mix" is confusing, you could easily set up a view that converts all the datetimeoffset's to UTC datetimes. > I am not saying there isn't a use case for something like > datetimeoffset, I think that there is. For example in some kind of Surely the fact that you'll lose data if you try to store a common .NET datatype with any kind of ORM (eg. EF, which is pretty popular) right now, using "the world's most advanced open source relational database", is reason enough to support it? -- Best regards, Jeremy Morton (Jez)
On 4/17/20 11:00 AM, Jeremy Morton wrote: >> I am not saying there isn't a use case for something like >> datetimeoffset, I think that there is. For example in some kind of > > Surely the fact that you'll lose data if you try to store a common .NET > datatype with any kind of ORM (eg. EF, which is pretty popular) right > now, using "the world's most advanced open source relational database", > is reason enough to support it? No, because if PostgreSQL started adding supports for all data types in all standard libraries of all programming languages it would become virtually unusable. What if PostgreSQL shipped with 8 or 9 different timestamp types? How would the users be able to pick which one to use? It is better to have a few types which cover the use cases of most users and then let extension authors add more specialized types. Andreas
Jeremy Morton <postgres@game-point.net> writes:
> Surely the fact that you'll lose data if you try to store a common
> .NET datatype with any kind of ORM (eg. EF, which is pretty popular)
> right now, using "the world's most advanced open source relational
> database", is reason enough to support it?
If the ORM somehow prevents you from using timestamptz, that's a
bug in the ORM. If it doesn't, the above is just a hysterical
claim with no factual foundation.
regards, tom lane
How could the ORM use timestamptz when that doesn't actually store both a datetime and an offset? -- Best regards, Jeremy Morton (Jez) Tom Lane wrote: > Jeremy Morton <postgres@game-point.net> writes: >> Surely the fact that you'll lose data if you try to store a common >> .NET datatype with any kind of ORM (eg. EF, which is pretty popular) >> right now, using "the world's most advanced open source relational >> database", is reason enough to support it? > > If the ORM somehow prevents you from using timestamptz, that's a > bug in the ORM. If it doesn't, the above is just a hysterical > claim with no factual foundation. > > regards, tom lane >
On 2020-Apr-17, Jeremy Morton wrote: > How could the ORM use timestamptz when that doesn't actually store both a > datetime and an offset? There are lots of ways in which timestamptz can be used. The most typical one is to rely on the TimeZone configuration parameter; another very typical one is to have a zone specification at the end of the timestamp literal such as "+03" or "Europe/Madrid", as Andreas Karlsson already mentioned. In addition to those, the "AT TIME ZONE" operator can be used with a bare timestamp. The main point of the timestamptz type is that both the input and output are timezone-aware. This timezone is not *stored*, but in most cases it doesn't need to be. I have never seen a case where an application needed a timezone to be *stored* together with each timestamp value. It's just not useful. If you want to set up an output timezone, you can set it for each specific user (for example). Then all timestamps you show to that user will use that timezone. It's a very easy and convenient thing. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services