Is there anyway to extract only date from epoch time stamp in sql alchemy

Question

Function to convert given epoch timestamp to date.

def extract_date_from_epoch(epoch: int) -> str:
    return datetime.utcfromtimestamp(epoch).date()

I have an analytic table with the below columns in there.

class Analytic(Base):
    __tablename__ = 'analytic'

    id = Column(Integer, primary_key=True, autoincrement=True)
    record_timestamp = Column(Integer)
    consumed_time = Column(Integer)
    profile_id = Column(Integer, ForeignKey('profile.id'))
    conversation_id = Column(Integer, ForeignKey('conversation.id'))

I want to get the total consumed time and filter by profile id and date I gave to it. also, results should be grouped by conversation_id.

Here is my current query without the date filter.

analytic_get_stmt = select(Analytic.conversation_id,func.sum(Analytic.consumed_time).label("total_time")).where(Analytic.profile_id == profile_id).group_by(Analytic.conversation_id)

So is there any method I can filter the data by date? If not I think the best method is to get all the data and filter it using Python without the SQL like below.

analytic_get_stmt = select(Analytic.conversation_id, Analytic.record_timestamp, Analytic.consumed_time). \
            where(Analytic.profile_id == profile_id)
        analytic_data = self.session.execute(analytic_get_stmt)

        conversation_ids = []
        total_consumed_time = 0

        for data in analytic_data:
            if extract_date_from_epoch(data.record_timestamp) == date:
                if data.conversation_id not in conversation_ids:
                    conversation_ids.append(data.conversation_id)
                total_consumed_time += data.consumed_time

I'm using the Postgres database locally. – Malith Dilshan Aug 10 '23 at 07:34 — Malith Dilshan, Aug 10 '23 at 07:34

score 0 · Answer 1 · answered Aug 10 '23 at 18:30

As shown here, you can convert an epoch timestamp to a date like this:

select to_timestamp(record_timestamp)::date from analytic;

The equivalent SQLAlchemy expression would be

sa.select(sa.cast(sa.func.to_timestamp(Analytic.record_timestamp), sa.Date))

Thus your GROUP BY query would be

analytic_get_stmt = (
    sa.select(
        Analytic.conversation_id,
        sa.func.sum(Analytic.consumed_time).label('total_time'),
    )
    .where(
        sa.and_(
            Analytic.profile_id == profile_id,
            sa.cast(sa.func.to_timestamp(Analytic.record_timestamp), sa.Date)
            == date,
        )
    )
    .group_by(Analytic.conversation_id)
)

Using the expression in a WHERE clause will not be very performant, so it would be good to add a functional index that pre-computed the date values. Unfortunately, PostgreSQL does not seem to allow this, perhaps because the timezone of the timestamp is no known with certainty.

Is there anyway to extract only date from epoch time stamp in sql alchemy

1 Answers1