Annotate multiple values as JSONObject - parse datetime

Question

I'm annotating my QuerySet as follows, using JSONObject, to obtain multiple fields from Scan:

MyModel.objects.annotate(
  myscan=Subquery(
      Scan.objects
      .all()
      .values(data=JSONObject(
          id='id',
          nin='nin',
          datetime='datetime')
      )[:1]
  )
)

I want to get a proper datetime representation though, somehow.

When I try to use myscan.datetime, I get the string representation (e.g. datetime': '2024-12-31T09:19:30.221953+00:00. How can I either return the datetime object itself (instead of a string; assuming this is impossible when using JSONObject) or alternatively do a strptime() before returning the string as part of the JSON?

Obviously I could parse the string, convert to datetime and then do strptime() again, but that seems like a dirty solution. Any suggestions?

JSON has no datetime type, BSON has, but for some reason it is not included, probably because not all database support a BSON type. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jan 7 at 16:17

SaeX · Accepted Answer · 2025-01-07 16:24:40Z

0

When using PostgreSQL, the datetime format can be parsed/casted as follows (using the Postgres format):

from django.db.models.functions import Cast
from django.db.models import Subquery, F, Value, Func, CharField

MyModel.objects.annotate(
  myscan=Subquery(
      Scan.objects
      .all()
      .values(data=JSONObject(
          id='id',
          nin='nin',
          datetime=Cast(
              Func(
                  F('datetime'),
                  Value('DD-MM-YYYY HH24:MI'),
                  function='TO_CHAR'
              ),
              output_field=CharField()
          )
        )
      )[:1]
  )
)

answered Jan 7 at 16:24

SaeX

19k18 gold badges81 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Annotate multiple values as JSONObject - parse datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related