#30224: Mysql Datetime value is string instead of datetime object
-------------------------------------+-------------------------------------
     Reporter:  Martin Kuhn          |                    Owner:  nobody
         Type:  Bug                  |                   Status:  closed
    Component:  Database layer       |                  Version:  2.2
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  needsinfo
     Keywords:  Mysql                |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Martin Kuhn):

 Ok, I managed to narrow down the problem. My Query is using annotations to
 use the aggregation function MIN.

 {{{
 Project.objects \
         .prefetch_related('buyout_set') \
         .prefetch_related('teammember_set') \
         .annotate(min_expiry_date=Min('buyout__expiry_date',
                                       filter=Q(buyout__is_archived=False,
 buyout__is_deleted=False))
                   ) \
         .filter(Q(min_expiry_date__gte=(now + expiryInterval))
                 & Q(min_expiry_date__lte=(now + expiryInterval +
 timedelta(days=1))))

 }}}

 I tested the generated query below using  `mysqlclient` and found that it
 does return the annotated value as a string. The problem is a custom
 annotation "MIN(Date)". Probably mysqlclient has no type info for this
 custom value, since it could be any/multiple types. (E.g. using an IF
 statement.)
 However this auto generated query could be rewritten to use CAST(
 "annotation" as DateTime).


 The resulting query is
 ----

 {{{
 SELECT
     `advertisement_project`.`id`,
     `advertisement_project`.`created_on`,
     `advertisement_project`.`modified_on`,
     `advertisement_project`.`is_deleted`,
     `advertisement_project`.`created_by_id`,
     `advertisement_project`.`modified_by_id`,
     `advertisement_project`.`name`,
     `advertisement_project`.`code`,
     `advertisement_project`.`spot_length`,
     `advertisement_project`.`eta`,
     `advertisement_project`.`spot_id`,
     `advertisement_project`.`spot_id_suffix`,
     `advertisement_project`.`folder_id`,
     `advertisement_project`.`system_id`,
     `advertisement_project`.`status`,
     `advertisement_project`.`type`,
     `advertisement_project`.`ad_type`,
     `advertisement_project`.`parent_project_id`,
     `advertisement_project`.`original_project_id`,
     `advertisement_project`.`territory_id`,
     `advertisement_project`.`language_id`,
     `advertisement_project`.`campaign_id`,
     `advertisement_project`.`preview_id`,
     `advertisement_project`.`links_id`,
     `advertisement_project`.`producer_names`,
     MIN(CASE
         WHEN
             (`buyout_buyout`.`is_archived` = 0
                 AND `buyout_buyout`.`is_deleted` = 0)
         THEN
             `buyout_buyout`.`expiry_date`
         ELSE NULL
     END) AS `minExpiryDate`
 FROM
     `advertisement_project`
         LEFT OUTER JOIN
     `buyout_buyout` ON (`advertisement_project`.`id` =
 `buyout_buyout`.`project_id`)
 GROUP BY `advertisement_project`.`id`
 HAVING (MIN(CASE
     WHEN
         (`buyout_buyout`.`is_archived` = 0
             AND `buyout_buyout`.`is_deleted` = 0)
     THEN
         `buyout_buyout`.`expiry_date`
     ELSE NULL
 END) >= '2019-03-01 00:00:00'
     AND MIN(CASE
     WHEN
         (`buyout_buyout`.`is_archived` = 0
             AND `buyout_buyout`.`is_deleted` = 0)
     THEN
         `buyout_buyout`.`expiry_date`
     ELSE NULL
 END) <= '2019-03-02 00:00:00')
 ORDER BY NULL;
 }}}

-- 
Ticket URL: <https://code.djangoproject.com/ticket/30224#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.685849d8d4c59bed37021dd4bb0182cb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to