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 2 days ago
rishabh-lok
32
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 2 days ago
rishabh-lok
32
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Why don't you then just use the single argument form ofto_timestamp()?
– Ilja Everilä
2 days ago
@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
2 days ago
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ä
2 days ago
@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
2 days ago
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
2 days ago
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 2 days ago
rishabh-lok
32
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 2 days ago
rishabh-lok
32
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
rishabh-lok
32
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
asked 2 days ago
rishabh-lok
32
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
rishabh-lok
32
asked 2 days ago
rishabh-lok
32
32
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
rishabh-lok is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Why don't you then just use the single argument form ofto_timestamp()?
– Ilja Everilä
2 days ago
@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
2 days ago
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ä
2 days ago
@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
2 days ago
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
2 days ago
add a comment |
Why don't you then just use the single argument form ofto_timestamp()?
– Ilja Everilä
2 days ago
@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
2 days ago
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ä
2 days ago
@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
2 days ago
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
2 days ago
Why don't you then just use the single argument form of
to_timestamp()?– Ilja Everilä
2 days ago
Why don't you then just use the single argument form of
to_timestamp()?– Ilja Everilä
2 days ago
@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
2 days ago
@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
2 days ago
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ä
2 days ago
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ä
2 days ago
@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
2 days ago
@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
2 days ago
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
2 days ago
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
2 days ago
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 2 days ago
Ilja Everilä
22.5k33459
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
yesterday
What do you mean by "exact date"?
– Ilja Everilä
21 hours ago
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
19 hours ago
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 2 days ago
Ilja Everilä
22.5k33459
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
yesterday
What do you mean by "exact date"?
– Ilja Everilä
21 hours ago
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
19 hours ago
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 2 days ago
Ilja Everilä
22.5k33459
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
yesterday
What do you mean by "exact date"?
– Ilja Everilä
21 hours ago
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
19 hours ago
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 2 days ago
Ilja Everilä
22.5k33459
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 2 days ago
Ilja Everilä
22.5k33459
answered 2 days ago
Ilja Everilä
22.5k33459
answered 2 days ago
Ilja Everilä
22.5k33459
answered 2 days ago
Ilja Everilä
22.5k33459
22.5k33459
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
yesterday
What do you mean by "exact date"?
– Ilja Everilä
21 hours ago
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
19 hours ago
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
yesterday
What do you mean by "exact date"?
– Ilja Everilä
21 hours ago
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
19 hours ago
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
yesterday
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
yesterday
What do you mean by "exact date"?
– Ilja Everilä
21 hours ago
What do you mean by "exact date"?
– Ilja Everilä
21 hours ago
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
19 hours ago
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
19 hours ago
add a comment |
rishabh-lok is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
rishabh-lok is a new contributor. Be nice, and check out our Code of Conduct.
rishabh-lok is a new contributor. Be nice, and check out our Code of Conduct.
rishabh-lok is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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ä
2 days ago
@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
2 days ago
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ä
2 days ago
@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
2 days ago
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
2 days ago