Sqlalchemy convert epoch time to date in group by
up vote
0
down vote
favorite
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()
python python-2.7 orm sqlalchemy flask-sqlalchemy
asked Nov 19 at 9:28
rishabh-lok
33
add a comment |
up vote
0
down vote
favorite
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()
python python-2.7 orm sqlalchemy flask-sqlalchemy
asked Nov 19 at 9:28
rishabh-lok
33
Why don't you then just use the single argument form ofto_timestamp()?
– Ilja Everilä
Nov 19 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 at 11:36
funcin 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 at 11:53
@IljaEverilä Thanks, I am able to get but still I am not able to extract date from it.. Below is the queryfunc.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 at 12:08
Using the above query I am not able to perform group_by as date is returned as an instance of datetime likedatetime.datetime(2018, 4, 9, 7, 34, 2, 744000)
– rishabh-lok
Nov 19 at 12:19
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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()
python python-2.7 orm sqlalchemy flask-sqlalchemy
asked Nov 19 at 9:28
rishabh-lok
33
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()
python python-2.7 orm sqlalchemy flask-sqlalchemy
python python-2.7 orm sqlalchemy flask-sqlalchemy
asked Nov 19 at 9:28
rishabh-lok
33
asked Nov 19 at 9:28
rishabh-lok
33
edited Nov 19 at 10:18
asked Nov 19 at 9:28
rishabh-lok
33
asked Nov 19 at 9:28
rishabh-lok
33
asked Nov 19 at 9:28
rishabh-lok
33
33
Why don't you then just use the single argument form ofto_timestamp()?
– Ilja Everilä
Nov 19 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 at 11:36
funcin 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 at 11:53
@IljaEverilä Thanks, I am able to get but still I am not able to extract date from it.. Below is the queryfunc.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 at 12:08
Using the above query I am not able to perform group_by as date is returned as an instance of datetime likedatetime.datetime(2018, 4, 9, 7, 34, 2, 744000)
– rishabh-lok
Nov 19 at 12:19
add a comment |
Why don't you then just use the single argument form ofto_timestamp()?
– Ilja Everilä
Nov 19 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 at 11:36
funcin 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 at 11:53
@IljaEverilä Thanks, I am able to get but still I am not able to extract date from it.. Below is the queryfunc.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 at 12:08
Using the above query I am not able to perform group_by as date is returned as an instance of datetime likedatetime.datetime(2018, 4, 9, 7, 34, 2, 744000)
– rishabh-lok
Nov 19 at 12:19
Why don't you then just use the single argument form of
to_timestamp()?– Ilja Everilä
Nov 19 at 11:30
Why don't you then just use the single argument form of
to_timestamp()?– Ilja Everilä
Nov 19 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 at 11:36
@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 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 at 11:53
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 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 at 12:08
@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 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 at 12:19
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 at 12:19
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
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))
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
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 at 5:43
What do you mean by "exact date"?
– Ilja Everilä
Nov 21 at 7: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 at 8:38
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
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))
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
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 at 5:43
What do you mean by "exact date"?
– Ilja Everilä
Nov 21 at 7: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 at 8:38
add a comment |
up vote
2
down vote
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))
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
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 at 5:43
What do you mean by "exact date"?
– Ilja Everilä
Nov 21 at 7: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 at 8:38
add a comment |
up vote
2
down vote
up vote
2
down vote
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))
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
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))
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
answered Nov 19 at 12:30
Ilja Everilä
22.6k33459
22.6k33459
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 at 5:43
What do you mean by "exact date"?
– Ilja Everilä
Nov 21 at 7: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 at 8:38
add a comment |
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 at 5:43
What do you mean by "exact date"?
– Ilja Everilä
Nov 21 at 7: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 at 8:38
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 at 5:43
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 at 5:43
What do you mean by "exact date"?
– Ilja Everilä
Nov 21 at 7:09
What do you mean by "exact date"?
– Ilja Everilä
Nov 21 at 7: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 at 8:38
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 at 8:38
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371655%2fsqlalchemy-convert-epoch-time-to-date-in-group-by%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why don't you then just use the single argument form of
to_timestamp()?– Ilja Everilä
Nov 19 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 at 11:36
funcin 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 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 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 at 12:19