"""use UTC DateTime in DB
Revision ID: 4a3773e332a0
Revises:
Create Date: 2022-11-15 17:35:11.717714
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '4a3773e332a0'
down_revision = None
branch_labels = None
depends_on = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('borrows', sa.Column('purchase_utc', sa.DateTime(), nullable=True))
op.add_column('borrows', sa.Column('expiration_utc', sa.DateTime(), nullable=True))
op.add_column('borrows', sa.Column('purchase_timezone', sa.Integer(), nullable=True))
op.add_column('borrows', sa.Column('expiration_timezone', sa.Integer(), nullable=True))
borrows = sa.Table(
"borrows",
sa.MetaData(),
sa.Column("id", sa.Integer, primary_key=True, nullable=False),
sa.Column("purchase", sa.String, nullable=True),
sa.Column("expiration", sa.String, nullable=True),
sa.Column("purchase_utc", sa.DateTime, nullable=True),
sa.Column("expiration_utc", sa.DateTime, nullable=True),
sa.Column("purchase_timezone", sa.Integer, nullable=True),
sa.Column("expiration_timezone", sa.Integer, nullable=True)
)
connection = op.get_bind()
results = connection.execute(sa.select([
borrows.c.id,
borrows.c.purchase,
borrows.c.expiration,
borrows.c.purchase_utc,
borrows.c.expiration_utc,
borrows.c.purchase_timezone,
borrows.c.expiration_timezone
])).fetchall()
from datetime import datetime, timezone
for id, purchase, expiration, purchase_utc, expiration_utc, purchase_timezone, expiration_timezone in results:
if id % 1000 == 0:
print(f"... obdelujem id {id}", end="\r")
if purchase == None:
print(f"at id {id} purchase is None")
continue
purchase_utc = datetime.strptime(purchase, "%Y-%m-%dT%H:%M:%S%z")
expiration_utc = datetime.strptime(expiration, "%Y-%m-%dT%H:%M:%S%z")
purchase_timezone = purchase_utc.tzinfo.utcoffset(None).seconds
expiration_timezone = expiration_utc.tzinfo.utcoffset(None).seconds
purchase_utc = purchase_utc.astimezone(timezone.utc).replace(tzinfo=None)
expiration_utc = expiration_utc.astimezone(timezone.utc).replace(tzinfo=None)
connection.execute(borrows.update().where(borrows.c.id == id).values(
purchase_utc = purchase_utc,
expiration_utc = expiration_utc,
purchase_timezone = purchase_timezone,
expiration_timezone = expiration_timezone
))
op.drop_column('borrows', 'expiration')
op.drop_column('borrows', 'purchase')
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('borrows', sa.Column('purchase', sa.VARCHAR(), nullable=True))
op.add_column('borrows', sa.Column('expiration', sa.VARCHAR(), nullable=True))
borrows = sa.Table(
"borrows",
sa.MetaData(),
sa.Column("id", sa.Integer, primary_key=True, nullable=False),
sa.Column("purchase", sa.String, nullable=True),
sa.Column("expiration", sa.String, nullable=True),
sa.Column("purchase_utc", sa.DateTime, nullable=True),
sa.Column("expiration_utc", sa.DateTime, nullable=True),
sa.Column("purchase_timezone", sa.Integer, nullable=True),
sa.Column("expiration_timezone", sa.Integer, nullable=True)
)
connection = op.get_bind()
results = connection.execute(sa.select([
borrows.c.id,
borrows.c.purchase,
borrows.c.expiration,
borrows.c.purchase_utc,
borrows.c.expiration_utc,
borrows.c.purchase_timezone,
borrows.c.expiration_timezone
])).fetchall()
from datetime import datetime, timezone, timedelta
for id, purchase, expiration, purchase_utc, expiration_utc, purchase_timezone, expiration_timezone in results:
if id % 1000 == 0:
print(f"... obdelujem id {id}", end="\r")
if purchase_utc == None:
print(f"at id {id} purchase_utc is None")
continue
connection.execute(borrows.update().where(borrows.c.id == id).values(
purchase = purchase_utc.astimezone(timezone(timedelta(seconds=purchase_timezone))).isoformat(),
expiration = expiration_utc.astimezone(timezone(timedelta(seconds=expiration_timezone))).isoformat()
))
op.drop_column('borrows', 'expiration_timezone')
op.drop_column('borrows', 'purchase_timezone')
op.drop_column('borrows', 'expiration_utc')
op.drop_column('borrows', 'purchase_utc')
# ### end Alembic commands ###
