Support for DATETIMEOFFSET

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:

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 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.

1. Not sure I understand. I’ve never used datetimeoffset so please bear with me. How does storing a time zone with the date time “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 zone in 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

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 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.

1. Not sure I understand. I’ve never used datetimeoffset so please bear with me. How does storing a time zone with the date 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 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.

1. Not sure I understand. I’ve never used datetimeoffset so please bear with me. How does storing a time zone with the date 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.

In PostgreSQL there are two types; timestamp and timestamptz. If you use timestamptz then all time stamps coming into the database with time zones will be converted to and stored in UTC in the database and all times coming out of the database will 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:

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