12

this is my code, query Notification.create_time

result = session.query(
        Notification.content, cls.is_read,
        Notification.create_time).join(
        cls, Notification.id == cls.notification).filter(
            and_(cls.user == user_id)).order_by(
                Notification.create_time.desc()).all()

in other place need to json.dumps query result to frontend, datetime format cann't json.dumps, so I want to do like this:

session.query(Notification.create_time.strftime('%Y-%m-%d %H:%M'))

so, how to change datetime to string in sqlalchemy query?

davidism
  • 121,510
  • 29
  • 395
  • 339
max
  • 187
  • 1
  • 2
  • 9
  • 1
    That'd be DB specific. Postgresql for example uses [`to_char`](https://www.postgresql.org/docs/current/static/functions-formatting.html) for timestamp formatting, which you could call using [`sqlalchemy.func`](http://docs.sqlalchemy.org/en/latest/core/functions.html). But the (imo) better solution would be to [use a JSON serializer that handles `datetime` objects](http://stackoverflow.com/questions/23285558/datetime-date2014-4-25-is-not-json-serializable-in-django). – Ilja Everilä Oct 10 '16 at 14:09
  • you are awesome, thank you very much.my idea go to a wrong way.XD – max Oct 11 '16 at 02:03

1 Answers1

16

Can use func.to_char() (in postgres, not sure about mysql) to convert a datetime to a string, something like:

from sqlalchemy.sql import func
session.query(func.to_char(Notification.create_time, '%Y-%m-%d %H:%M'))

But the better way it to set up your json serializer to handle things like datetimes, as is described in this answer

Community
  • 1
  • 1
reptilicus
  • 10,290
  • 6
  • 55
  • 79