2023-04-21

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: 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)




No comments:

Post a Comment