SQLAlchemy Filter Query over Datetime timezone
I am trying to filter data with timezone. But the query is filtering data based on the datetime object values.
My Entire Code is :
from sqlalchemy import Column, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
import datetime
import pytz
engine = create_engine('sqlite:///mydatabase121.db', echo=True)
Base = declarative_base()
# Creating table over Base
class MyTable(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True))
Base.metadata.create_all(engine)
# Creating Session
Session = sessionmaker(bind=engine)
session = Session()
#Inserting Data with Timezone in the table
# Create a new record with a DateTime value of January 1st, 2024 at 10:00 AM in the US/Eastern timezone
created_at = pytz.timezone('US/Eastern').localize(datetime.datetime(2024, 1, 1, 10, 0))
record = MyTable(created_at=created_at)
session.add(record)
session.commit()
# Create a new record with a DateTime value of January 1st, 2024 at 11:00 AM in the UTC timezone
created_at = pytz.timezone('UTC').localize(datetime.datetime(2024, 1, 1, 11, 0))
record = MyTable(created_at=created_at)
session.add(record)
session.commit()
# Create a new record with a DateTime value of January 1st, 2024 at 12:00 AM in the US/Eastern timezone
created_at = pytz.timezone('US/Eastern').localize(datetime.datetime(2024, 1, 1, 12, 0))
record = MyTable(created_at=created_at)
session.add(record)
session.commit()
#Filtering the data based on DateTime values in a specific timezone.
# Query for all records in the table that have a DateTime value of January 1st, 2024 at 10:00 AM in the US/Eastern timezone
records = session.query(MyTable).filter(MyTable.created_at == pytz.timezone('US/Eastern').localize(datetime.datetime(2024, 1, 1, 10, 0))).all()
# Print the records
print("\nPrinting Record : \n")
for record in records:
print(record.id, record.created_at)
# Convert a DateTime value that represents first data in record in the US/Eastern timezone to the UTC timezone
def convert_datetime_timezone(dt, tz1, tz2):
tz1 = pytz.timezone(tz1)
tz2 = pytz.timezone(tz2)
dt = dt.astimezone(tz1)
dt = tz2.normalize(dt.astimezone(tz2))
dt = dt.strftime("%Y-%m-%d %H:%M:%S")
return dt
converted = convert_datetime_timezone(records[0].created_at, 'US/Eastern', 'UTC')
converted2 = convert_datetime_timezone(records[0].created_at, 'US/Eastern', 'Asia/Kolkata')
# Print the converted DateTime value
print("\nDatetime Converted in UTC Timezone: \n")
print(converted)
print("\nDatetime Converted in Indian Timezone: \n")
print(converted2)
The query should filter only those data that has datetimevalues equals to what is in the query and also timezone mentioned. But Output:
THe code is not filtering data with timezone it is filtering data based on the datetime object values.
I want to improve the filter function so that different it can filter data with timezone.
Above is the screenshot that is inserting 5 data and if we am query data base on UTC,US/Eastern than the record which we are getting can not be 3,4,5.
records = session.query(MyTable).filter(
MyTable.created_at == pytz.timezone(
'US/Eastern').localize(datetime.datetime(2025, 1, 1, 11, 0))
).all()
# Print the records
print("\nPrinting Record : \n")
for record in records:
print(record.id, record.created_at)
Comments
Post a Comment