SQLAlchemy DateTime timezone

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily accept someone passing in a naive localtime-based datetime, even if I used timezone=True with it, because it makes local or UTC time non-naive without having a base timezone to normalize it with. I have tried (using pytz) to make the datetime object non-naive, but when I save this to the DB it comes back as naive.

Note how datetime.datetime.utcnow does not work with timezone=True so well:

import sqlalchemy as sa
from sqlalchemy.sql import select
import datetime

metadata = sa.MetaData('postgres://user:pass@machine/db')

data_table = sa.Table('data', metadata,
    sa.Column('id',   sa.types.Integer, primary_key=True),
    sa.Column('date', sa.types.DateTime(timezone=True), default=datetime.datetime.utcnow)
)

metadata.create_all()

engine = metadata.bind
conn = engine.connect()
result = conn.execute(data_table.insert().values(id=1))

s = select([data_table])
result = conn.execute(s)
row = result.fetchone()

(1, datetime.datetime(2009, 1, 6, 0, 9, 36, 891887))

row[1].utcoffset()

datetime.timedelta(-1, 64800) # that's my localtime offset!!

datetime.datetime.now(tz=pytz.timezone("US/Central"))

datetime.timedelta(-1, 64800)

datetime.datetime.now(tz=pytz.timezone("UTC"))

datetime.timedelta(0) #UTC

Even if I change it to explicitly use UTC:

...

data_table = sa.Table('data', metadata,
    sa.Column('id',   sa.types.Integer, primary_key=True),
    sa.Column('date', sa.types.DateTime(timezone=True), default=datetime.datetime.now(tz=pytz.timezone('UTC')))
)

row[1].utcoffset()

...

datetime.timedelta(-1, 64800) # it did not use the timezone I explicitly added

Or if I drop the timezone=True:

...

data_table = sa.Table('data', metadata,
    sa.Column('id',   sa.types.Integer, primary_key=True),
    sa.Column('date', sa.types.DateTime(), default=datetime.datetime.now(tz=pytz.timezone('UTC')))
)

row[1].utcoffset() is None

...

True # it didn't even save a timezone to the db this time


Asked by: Alfred817 | Posted: 06-12-2021






Answer 1

http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html#DATATYPE-TIMEZONES

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

The only way to store it with postgresql is to store it separately.

Answered by: Marcus543 | Posted: 07-01-2022



Answer 2

One way to solve this issue is to always use timezone-aware fields in the database. But note that the same time can be expressed differently depending on the timezone, and even though this is not a problem for computers it is very inconvenient for us:

2003-04-12 23:05:06 +01:00
2003-04-13 00:05:06 +02:00 # This is the same time as above!

Also Postgresql stores all timezone-aware dates and times internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

Instead, I recommend to use UTC timestamps both throughout the app and timezone-naive dates and times in the database, and only convert them to users local timezone before user sees them.

This strategy lets you have the cleanest code, avoiding any timezone conversions and confusions, and makes your database and app work consistently independent of the "local timezone" differences. For example, you might have your development machine and production server running on cloud in different timezones.

To achieve this, tell Postgresql that you want to see timezones in UTC before initializing the engine.

In SqlAlchemy you do it like this:

engine = create_engine(..., connect_args={"options": "-c timezone=utc"})

And if you are using tornado-sqlalchemy you can use:

factory = make_session_factory(..., connect_args={"options": "-c timezone=utc"})

Since we use all UTC timezones everywhere, we simply use timezone-naive dates and times in the model:

created_at = Column(DateTime, default=datetime.utcnow)
updated_at = Column(DateTime)

And the same in case if you are using alembic:

sa.Column('created_at', sa.DateTime()),
sa.Column('updated_at', sa.DateTime()),

And in the code use UTC time:

from datetime import datetime
...
model_object.updated_at = datetime.now(timezone.utc)

Answered by: Emily760 | Posted: 07-01-2022



Answer 3

a solution is given in this question’s answer:

you can circumvent that by storing all (date)time objects in your database in UTC, and converting the resulting naïve datetime objects to aware ones on retrieval.

the only drawback is that you lose timezone information, but it’s probably a good idea to store your datetime objects in utc, anyway.

if you care about the timezone information, i’d store it separately, and only convert the utc to local time in the last possible instance (e.g. right before displaying)

or maybe you don’t need to care after all, and can use local timezone information from the machine you run your progam on, or the user’s browser if it’s a webapp.

Answered by: Gianna325 | Posted: 07-01-2022



Answer 4

The following structure is recommended to store UTC date and time data in the database as well as to prevent data storage that does not have such location information.

import datetime
from sqlalchemy import DateTime
from sqlalchemy.types import TypeDecorator

    
class TZDateTime(TypeDecorator):
    """
    A DateTime type which can only store tz-aware DateTimes.
    """
    impl = DateTime(timezone=True)

    def process_bind_param(self, value, dialect):
        if isinstance(value, datetime.datetime) and value.tzinfo is None:
            raise ValueError('{!r} must be TZ-aware'.format(value))
        return value

    def __repr__(self):
        return 'TZDateTime()'

The values stored in the database should be defined as follows:

import datetime

import pytz


def tzware_datetime():
    """
    Return a timezone aware datetime.

    :return: Datetime
    """
    return datetime.datetime.now(pytz.utc)

Answered by: Alfred663 | Posted: 07-01-2022



Similar questions

python - Can SQLAlchemy DateTime Objects Only Be Naive?

I am working with SQLAlchemy, and I'm not yet sure which database I'll use under it, so I want to remain as DB-agnostic as possible. How can I store a timezone-aware datetime object in the DB without tying myself to a specific database? Right now, I'm making sure that times are UTC before I store them in the DB, and converting to localized at display-time, but that feels inelegant and brittle. Is there a DB-agnostic way...


python - datetime in defining database using sqlalchemy

Should I use () with datetime.now in defining tables? What code wrong 1 or 2? 1: Base = declarative_base() class T(Base): __tablename__ = 't' created = Column(DateTime, default=datetime.now) 2: Base = declarative_base() class T(Base): __tablename__ = 't' created = Column(DateTime, default=datetime.now())


python - SQLAlchemy default DateTime

This is my declarative model: import datetime from sqlalchemy import Column, Integer, DateTime from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Test(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) created_date = DateTime(default=datetime.datetime.utcnow) However, when I try to import this module, I get this error:


python - Compare DateTime and Interval in SQLAlchemy

I have this class: class Monitor(db.Model): ''' Base Monitor class. ''' __tablename__ = 'monitor' id = db.Column(db.Integer(), primary_key=True) last_checked = db.Column(db.DateTime(timezone=False)) poll_interval = db.Column(db.Interval(), default=datetime.timedelta(seconds=300)) And I have this query where I attempt to return only ...


python - SQLAlchemy ORM DateTime with specific format

I have a table which stores a datetime value in the following format: (e.g.) 20160213145512. Unfortunately, SQLAlchemy gives me no chance to specify the format used to store a datetime value into the database with sqlalchemy´s DateTime type. Therefore, i have created the following dataype: class Timestamp(TypeDecorator): impl = String(14) def process_bind_param(self, value, dialect): ...


python - How to filter datetime field +/- datetime using SQLalchemy

I have a mysql table representing editorial articles and their metadata like title, author, and datecreated. I have another table representing metrics (such as view counts) about those articles computed at different time points. Each row is a recording of these metrics for a particular article at a particular moment in time. I want to retrieve all rows of the metrics table where the metric row time...


python - How do I filter a datetime field using SQLAlchemy?

I've written the following SQLAlchemy query: db.get_session().query(Order, Order.created, Order.symbol).filter(Order.symbol=='GE').all() Which produces an output like this: What I'm trying to do is is get the ones where


python - SQLAlchemy nearest datetime

Is their any simple (fast) way to fetch a table and find the datetime nearest from a given dateTime in SQLAlchemy ? In most case the delta will be seconds between the datetime given and the one in the table. The date column is the primary key EDIT : I'm using SQLite


Sqlalchemy python how to query using IN over list of datetime

I would like to filter using a list of timestamps. In SQL the syntax would be like SELECT * FROM TABLE WHERE TABLE.QueryTimestamp IN ("2017-10-20 23:20:00", "2017-10-10 23:20:00") The column QueryTimestamp is datetime in Python. I tried using following which does not provide correct answers. ts_list is a list of python datetime values. session.query(TABLE).filter(TA...


python - Datetime not updating on insert using SQLAlchemy on MSSQL

I am using SQLAlchemy (1.2.5), pyodbc(4.0.22). I have created a table using python / SQLAlchemy as follows: class Persons(Base): __tablename__ = 'Persons' ID_Person = Column(Integer(),primary_key = True) Affiliate_Name = Column(VARCHAR(200), unique=True, nullable = False) time_created = Column(DateTime(timezone=True), server_default=func.now()) time_updated = Column(DateTime(timezone=Tr...


python - How can I use UUIDs in SQLAlchemy?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?


python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?


python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...


python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?


python - SQLAlchemy and empty columns

When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance?


python - How can I get all rows with keys provided in a list using SQLalchemy?

I have sequence of IDs I want to retrieve. It's simple: session.query(Record).filter(Record.id.in_(seq)).all() Is there a better way to do it?


python - How can I order objects according to some attribute of the child in sqlalchemy?

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?


python - SQLAlchemy - INSERT OR REPLACE equivalent

does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language? Many thanks -- honzas


python - Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps. As a MySQL Date field, another mysql spec...


python - How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Suppose I have an engine pointing at MySQL database: engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True) I can populate dtdb with tables, FKs, etc by: metadata.create_all(engine) Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL sta...






Still can't find your answer? Check out these communities...



PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python



top