1

I am using Sqlalchemy as ORM for PSQL db. My timestamps are stores as epoch times in my database eg, 1525868337991. (in milli sec)

I am writing a query to get count of employees on a particular date(grouping by on date). I am not able to find any way by which, I can convert epoch to date in my ORM query, like psql has to_timestamp. The query is written below :

employees_details = db.session.query(
      func.count(EmployeeInfo.id).label("employee_count"), EmployeeInfo.employee_created_on, EmployeeSourceInfo.employee_source_display_name
    ).join(
      EmployeeSourceInfo, EmployeeInfo.lead_source_id == EmployeeSourceInfo.id
    ).group_by(func.as_utc(EmployeeInfo.employee_created_on), EmployeeSourceInfo.employee_source_display_name).all()
rishabh-lok
  • 23
  • 1
  • 6
  • Why don't you then just use the single argument form of `to_timestamp()`? – Ilja Everilä Nov 19 '18 at 11:30
  • @IljaEverilä I don't SqlAlchemy has to_timestamp() as its there psql. If its there, please let me know how to use it.. – rishabh-lok Nov 19 '18 at 11:36
  • `func` in SQLAlchemy is generic. You can use it to generate pretty much any function expression necessary. `func.xyzzy(1, 2)` is just fine, if your database has such a function. – Ilja Everilä Nov 19 '18 at 11:53
  • @IljaEverilä Thanks, I am able to get but still I am not able to extract date from it.. Below is the query `func.timezone('UTC',func.to_timestamp(EmployeeInfo.employee_created_on/1000))` I am dividing by 1000, as my timestamps are in milliseconds – rishabh-lok Nov 19 '18 at 12:08
  • Using the above query I am not able to perform group_by as date is returned as an instance of datetime like `datetime.datetime(2018, 4, 9, 7, 34, 2, 744000)` – rishabh-lok Nov 19 '18 at 12:19

1 Answers1

3

The func in SQLAlchemy is generic and can be used to produce almost any SQL function expression. With this in mind you can simply replace func.as_utc with

func.to_timestamp(EmployeeInfo.employee_created_on / 1000.0)

To then truncate it to a date either cast it as one:

from sqlalchemy import Date

func.to_timestamp(EmployeeInfo.employee_created_on / 1000.0).cast(Date)

or use the Postgresql specific function date_trunc() to reduce the resulting timestamp to day precision:

func.date_trunc('day', func.to_timestamp(EmployeeInfo.employee_created_on / 1000.0))
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • The former approach to cast dates is a good approach, but it returns date as an instance of datetime class. **datetime.date(2018, 4, 9)** . Is there any way to convert them to exact date in query itself ? Without that I have to run a loop and convert each date into **isoformat** – rishabh-lok Nov 20 '18 at 05:43
  • What do you mean by "exact date"? – Ilja Everilä Nov 21 '18 at 07:09
  • By **exact date** I mean in the format **2018-04-09** (YYYY-mm-dd) isoformat, whereas the cast query above returns date as an instance of datetime class **datetime.date(2018, 4, 9)** – rishabh-lok Nov 21 '18 at 08:38