It is better to store everything in UTC

While it's perfectly fine to just store stuff as utc in the database, a user shouldn't be forced to deal with timezone-naive datetimes in applciation code

#434 seems to be all over the place

While it's perfectly fine to just store stuff as utc in the database, a user shouldn't be forced to deal with timezone-naive datetimes in applciation code

I'm not sure I have good understanding of timezone-aware dates. What is the benefit of timezone-aware utc datetimes? User-friendly string representation?

It seems some databases preserve the timzeone - when you fetch the value you get the same tizmeone that was used to insert it, buuut postgres is not one of them so I personally don't care about this part.

I suspect that different people have widely different expectations about supporting of timezone-aware timestamps. Some may expect that database keep non-aware timestamps in UCT as Mike Bayer suggests in the above comment. Others may expect it uses timezone-aware database type. The same in Python, some users may expect they can assign datetimes in any timezones, not only UTC, and ORM makes some proper conversions (or even keep the timezone as it was provided) So it seems to me it may be hard to implement support of timezones which cover all expectations. What you want is only part of this functionality (attribute should accept only timezone-aware datetime values in UTC timezone only, reject all other timezones and local datetime values, and keep information in non-aware UTC column), but will it satisfy other users who want timezone support? I suspect that proper solution is way more complex than may be expected

Ok, so maybe the simplest non-error-prone way is: - add option tz_aware=True to datetime attributes - reject naive datetimes for such attributes - at the moment of assignment convert datetimes to utc - in the database store it to non-tz-aware timestamp column in utc - return it as tz-aware datetimes in utc timezone

If all datetimes are converted to utc internally at the first moment then probably we can use current implementation of operations, just don't allow mixing aware and non-aware attributes in the same expression

would is support value.astimzone(some_timzeone).date ?

It seems that tz-aware database columns may be tricky and error-prone, and many experienced developers recommend to avoid them

Imagine this: you store utc time in naive column. You apply astimzone to it to later extract date from it in a specific timezone. The server for some reason does not use UTC time and you get an unexpected result.

If we do conversion in Python and use naive timestamp in database it should not be a problem

In that scenario the .astimzone(some_timzeone).date happens in the database

If database uses naive time it should not be a problem in that use-case

If it is a naive date, does it matter how server is configured?

I think server configuration is important for timezone-aware database columns only

It does default to server one but you can change it as session level

If that value was read from database column, we assume it is in utc, because we always convert values to utc before saving them to database. Then we can typecast it to utc time inside expression and then convert to desired timezone

I don't remember all the details, but still think that timezone-aware database columns are pretty error-prone. Maybe look up it tomorrow

I assume half of those errors are human errors and other half only happens if you use something other than utc, probably variable offset tzs.

This is an article about timezone-aware timestamps in PostgreSQL https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/ And SQLAlchemy author's comment about it: https://news.ycombinator.com/item?id=19533216 """ I completely disagree with this advice: > However, when you come across timestamp, you can’t possibly know in what time zone the timestamp is just by looking at it. yes I do, it's in UTC, because I assume the application is written to correctly handle datetimes. > It is merely a wall time. Maybe it’s UTC, no, it's UTC. > or maybe the developer stored it in local time. right, maybe the application was not programmed correctly, that is always a possibility. But if they are using the timestamp with timezone datatype, it is 100% more likely they have made mistakes in doing so because it is much more confusing to have to fight against PostgreSQL's assumptions and implicit conversions, as the complexity of its behaviors and decisions using this datatype are described in this article. Does the application come up with the wrong answer if the server it runs on, or the webserver container, is mistakenly set to Eastern Timezone rather than UTC? Is the application ensuring that it knows the correct timezone of timestamps as they enter the application, and if so, why isn't it converting to UTC at that moment? Is the application otherwise receiving datetimes of arbitrary timezone, letting them move around business and display logic in their raw form, and waiting until they enter the database to be correctly normalized? that's too late. A correctly coded application does explicit normalization of datetime values the moment they enter the boundaries of the application, just like it would also convert encoded bytes into a unicode-aware strings up front, so that within the application there is no ambiguity, both when persisting and when handling. """

If we do conversion in Python and use naive timestamp in database it should not be a problem

1970-01-01 00:00:00 = "" 1970-01-01 00:00:01 = "🍌" 1970-01-01 00:00:02 = "🍌🍌"

Holy crap, ever noticed that small button in the python docs? It hides the ">>>" prefixes as well as the examples returns for easy copy.