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 yesterday
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 yesterday
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ä
yesterday
@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
yesterday
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ä
yesterday
@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
yesterday
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
yesterday
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 yesterday
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 yesterday
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 yesterday
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 yesterday
asked yesterday
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 yesterday
rishabh-lok
32
asked yesterday
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ä
yesterday
@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
yesterday
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ä
yesterday
@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
yesterday
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
yesterday
add a comment |
Why don't you then just use the single argument form ofto_timestamp()?
– Ilja Everilä
yesterday
@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
yesterday
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ä
yesterday
@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
yesterday
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
yesterday
Why don't you then just use the single argument form of
to_timestamp()?– Ilja Everilä
yesterday
Why don't you then just use the single argument form of
to_timestamp()?– Ilja Everilä
yesterday
@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
yesterday
@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
yesterday
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ä
yesterday
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ä
yesterday
@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
yesterday
@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
yesterday
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
yesterday
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
yesterday
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 yesterday
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ä
41 mins 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 yesterday
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ä
41 mins 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 yesterday
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ä
41 mins 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 yesterday
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 yesterday
Ilja Everilä
22.5k33459
answered yesterday
Ilja Everilä
22.5k33459
answered yesterday
Ilja Everilä
22.5k33459
answered yesterday
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ä
41 mins 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ä
41 mins 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ä
41 mins ago
What do you mean by "exact date"?
– Ilja Everilä
41 mins 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ä
yesterday
@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
yesterday
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ä
yesterday
@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
yesterday
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
yesterday